Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
incubusbaby
Starting Member
1 Post |
 Posted - 2004-06-10 : 02:48:43
|
| Two tables: contact and contact_number contact contains: contact_id (primary) firstname lastname Sample Data: contact_id firstname lastname 1 Brandon Boyd 2 Daniel Johns Contact_number contains: contact_id (primary) phone_type (primary) number (primary) (1=work, 2=home, 3=mobile) primary_number Sample Data: contact_id phone_type number primary_number 1 1 18461234561 Y 1 1 18461234562 N 1 2 18461234563 N 1 2 18461234564 Y 1 3 18461234565 N 2 1 18461234566 N 2 2 18461234567 N 2 3 18461234568 YI need to build an SQL statement that will generate the ff result set: Take note how primary_number was used. contact work home mobile Brandon Boyd 18461234561 18461234564 18461234565 Daniel Johns 18461234566 18461234567 18461234568How do i do this? |
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-06-10 : 07:44:23
|
| [code]select max(c.firstname + ' ' + c.lastname) as contact, max(case when cn.phone_type = 1 and cn.primary_number = 'Y' and num = 2 then cn.number when cn.phone_type = 1 and num < 2 then cn.number else '' end) work, max(case when cn.phone_type = 2 and cn.primary_number = 'Y' and num = 2 then cn.number when cn.phone_type = 2 and num < 2 then cn.number else '' end) home, max(case when cn.phone_type = 3 and cn.primary_number = 'Y' and num = 3 then cn.number when cn.phone_type = 3 and num < 2 then cn.number else '' end) mobilefrom contact cjoin contact_number cn on cn.contact_id = c.contact_idjoin( select contact_id,phone_type,count(1) as num from contact_number group by contact_id,phone_type) d on d.contact_id = cn.contact_id and d.phone_type = cn.phone_typegroup by d.contact_id[/code] |
 |
|
|
|
|
|
help needed