SQL query

[hanifa]

Hi guys,
Need some help in some query processing...
Here goes,this is the results i have obtained so far

            |id    |cat    | name |
              1     .....a        apple
              1     .....b        banana
              2     .....a        austria
              2     .....b        brazil
              2     .....c        china
              3     .....a         abraham
              3     .....c        clinton

Column cat can have at most 3 different types of values.. {a,b,c}

I wanna write a query such that the result comes out as such

<(....id       ......a         .......b.........        c..) corresponding to these fields.>

  |Col 1| Col 2  | Col 3  | Col 4|
....1    ...apple   ...banana
....2    ...austria .brazil...     china
....3    ...abraham           ..........clinton

Anyone can help...thanks guys

[campkev]

this is what you want

select a.id, a.name, b.name, c.name 
from tablename a
left join tablename b on a.id = b.id and b.cat = 'b'
left join tablename c on a.id = c.id and c.cat = 'c'
where a.cat = 'a'

[campkev]

actually, that one will only work if there is something in cat a

this will work better

select distinct main.id, a.name, b.name, c.name 
from tablename main
left join tablename a on main.id = a.id and a.cat = 'a'
left join tablename b on main.id = b.id and b.cat = 'b'
left join tablename c on main.id = c.id and c.cat = 'c'