0

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

Edited by pyTony: fixed formatting

2
Contributors
2
Replies
3
Views
11 Years
Discussion Span
Last Post by campkev
0

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'
0

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'
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.