Pivot Like Transact SQL

Question

I have a table like:

CustNumber      ContactType              ContactValue
      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


I want to return records like

CustNumber                 Phone1                    Phone2                      Email
    1123                         310-555-1012           213-421-5027         myContact@LA.com
    5678                         213-123-4567                                               Info@domain.com

Answer 1

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.

Answer 2

If you really want to imply the PIVOT operator and ContactValue does not contain anything else, then consider this approach too:

select CustNumber, Phone1, Phone2, Email
from
(
    select CustNumber, ContactValue, concat('Phone', row_number() over (partition by CustNumber order by ContactType)) as k
    from mytable
    where ContactValue not like '%@%'
    union all
    select CustNumber, ContactValue, 'Email'
    from mytable
    where ContactValue like '%@%'
) t
pivot
(
    max(ContactValue) for k in (Phone1, Phone2, Email)
)
u

You can easily adjust it for more phone and email columns.