There are a
lot of ways to do this. I'll show you two.
Code:
select p1.name,
( select phone_number from phone p2 where p2.person_id = p1.person_id and phone_type = 'direct' ) 'direct',
( select phone_number from phone p2 where p2.person_id = p1.person_id and phone_type = 'fax' ) 'fax',
( select phone_number from phone p2 where p2.person_id = p1.person_id and phone_type = 'cell' ) 'cell'
from persons p1
faster but tricky...
Code:
select p1.name,
max( case when phone_type = 'direct' then phone_number else '' end) 'direct',
max( case when phone_type = 'fax' then phone_number else '' end) 'fax',
max( case when phone_type = 'cell' then phone_number else '' end) 'cell'
from persons p1,
phone p2
where p2.person_id = p1.person_id
group by p1.name
There are many other ways... look at CROSSTAB.
Untested so there might be a typo or two. (In fact, I've edited to correct a couple already
)
Login
Register
FAQ
Search
