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 |
jchoudja
Starting Member
41 Posts |
Posted - 2013-02-22 : 11:38:29
|
Hi, I am trying to get a phone number list by location for each member.MemberMemberID|MemberName--------------------1_______|_Bean2_______|_Jonathan3_______|_Peter4_______|_AshleymemberPhoneMemberPhoneID|MemberID|PhoneID-------------------------------M1___________|_1______|p1M2___________|_1______|P2M3___________|_2______|P3M4___________|_3______|P4M5___________|_3______|P5M6___________|_3______|P6PhonePhoneID|PhoneLocationID|PhoneNumber|-------------------------------p1_____|_L1____________|_281 520 4444P2_____|_L3____________|_281 485 6656P3_____|_L1____________|_713 852 5253P4_____|_L1____________|_832 525 3333P5_____|_L3____________|_281 785 5225P6_____|_L5____________|_281 856 2323PhoneLocationPhoneLocationID|PhoneLocationName|PhonelLocationprecedenceL1_____________|CellPhone 1______|_6L2_____________|CellPhone 2______|_5L3_____________|Workphone 1______|_3L4_____________|Workphone 2______|_4L5_____________|Homephone 1______|_1L6_____________|Homephone 2______|_2L7_____________|Otherphone_______|_7I want to get a table like this: No Duplicate Rows. Each memeber should be in 1 row and all his phone numbers listed in the appropriate colum when thay are available.MemberPhonelistMemberID|MemberName|CellPhone_____|HomePhone_____|WorkPhone_____|Otherphone---------------------------------------------------------------------------1_______|_Bean_____|_281 520 4444_|______________|_281 485 6656_|__________2_______|_Jonathan_|_713 852 5253_|______________|______________|__________3_______|_Peter____|_832 525 3333_|_281 785 5225_|_281 856 2323_|__________4_______|_Ashley___|______________|______________|______________|__________I have done someting like this:CASE WHEN PhoneLocationPrecedence IN (1 , 2) THEN PhonePhoneNumber ELSE '-' END AS homephoneCASE WHEN PhoneLocationPrecedence IN (5 , 6) THEN PhonePhoneNumber ELSE '-' END As CellphoneCASE WHEN PhoneLocationPrecedence IN (3 , 4) THEN PhonePhoneNumber ELSE '-' END AS workphone But I am getting only one phone numer per row. If a member has more than 1 it will show phone numbers in many rowsHelp please.jc |
|
jchoudja
Starting Member
41 Posts |
Posted - 2013-02-22 : 11:39:43
|
Please read __ in my tables as Blancksjc |
|
|
jchoudja
Starting Member
41 Posts |
Posted - 2013-02-22 : 11:41:36
|
For now, you can ignore the other Phone location.jc |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2013-02-22 : 17:52:05
|
How do you intend multiple phones of the same type (e.g., CellPhone1 and CellPhone2) to be handled in the returned data? Should they be listed in separate columns? Concatenated together in one column? Other? In any event, you might want to make use of the PIVOT operator to transform the vertical list of phone number into a horizontal (single row) list. BOL has details.=================================================There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber |
|
|
jchoudja
Starting Member
41 Posts |
Posted - 2013-02-25 : 07:43:34
|
Phone numebers should be listed in seperate columns like in MemberPhonelist table above.jc |
|
|
jchoudja
Starting Member
41 Posts |
|
|
|
|