I 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.
I have done someting like this: CASE WHEN PhoneLocationPrecedence IN (1 , 2) THEN PhonePhoneNumber ELSE '-' END AS homephone CASE WHEN PhoneLocationPrecedence IN (5 , 6) THEN PhonePhoneNumber ELSE '-' END As Cellphone CASE 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 rows
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
Phone number list by location
Posted - 02/22/2013 : 11:38:29
