asked
Combine records
I have a query after joins some people have multiple phone numbers rt_telephone, which causes multiple results. How can I modify this query to create a phone_1 and phone_2 if there happens to be more than 1 phone number? Select ResidentKey, (rc_LastName) as 'Sort Name' ,(rc_FirstName + ' ' + rc_LastName) as Name ,rc_Address,rc_Address2,rc_City,rc_State,rc_Zipcode,u_StreetNumber,u_StreetName,u_UnitNumber,u_City,u_State,u_Zipcode,rt_Telephone,rc_email,co_name From dbo.ResidentContact JOIN dbo.unit ON (ResidentContact.ResidentKey = unit.UnitKey) JOIN dbo.Community ON (unit.CommunityKey = Community.CommunityKey) JOIN dbo.ResidentTelephone ON (ResidentContact.ResidentContactKey = ResidentTelephone.ResidentContactKey) Where ResidentKey='12245' Thanks, Kane
Here's an approach using a CTE with a row number: with PhoneCTE as ( select ResidentContactKey , rt_Telephone , row_number() over (partition by ResidentContactKey order by rt_Telephone) as RowNum from dbo.ResidentTelephone ) Select ResidentKey , (rc_LastName) as 'Sort Name' , (rc_FirstName + ' ' + rc_LastName) as Name , rc_Address ,rc_Address2 ,rc_City ,rc_State ,rc_Zipcode ,u_StreetNumber ,u_StreetName ,u_UnitNumber ,u_City ,u_State ,u_Zipcode ,isnull(PhoneCTE1.rt_Telephone, '') as Phone1 ,isnull(PhoneCTE2.rt_Telephone, '') as Phone2 ,rc_email ,co_name From dbo.ResidentContact JOIN dbo.unit ON (ResidentContact.ResidentKey = unit.UnitKey) JOIN dbo.Community ON (unit.CommunityKey = Community.CommunityKey) left join PhoneCTE PhoneCTE1 ON PhoneCTE1.ResidentContactKey = ResidentContact.ResidentContactKey and PhoneCTE1.RowNum = 1 left join PhoneCTE PhoneCTE2 ON PhoneCTE2.ResidentContactKey = ResidentContact.ResidentContactKey and PhoneCTE2.RowNum = 2 Where ResidentKey='12245'
18 People are following this question.
