Please try:
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
Output:
If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.
Regards
Echo
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.