declare @test table(CustNumber int,ContactType int,ContactValue char(25)) insert into @test values(1123,1,'310-555-1012'),(1123,2,'myContact@LA.com'), (1123,3,'213-421-5027'),(5678,1,'Info@domain.com'),(5678,3,'213-123-4567') ;with cte as(select CustNumber,ContactValue as Phone,row_number() over( partition by CustNumber order by CustNumber) rr from @test where ContactValue like '%[1-9]%') ,cte2 as (select CustNumber,ContactValue as Email from @test where ContactValue like '%@%') select c1.CustNumber,c1.Phone as Phone1,c12.Phone as Phone2,Email from cte c1 left join cte c12 on c1.rr=c12.rr-1 and c1.CustNumber=c12.CustNumber left join cte2 c2 on c1.CustNumber=c2.CustNumber where c1.rr=1
If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.