Pivot Like Transact SQL

asked 2021-03-19T07:47:37.18+00:00
Malam Malam 121 Reputation points

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
{count} votes

2 answers

Sort by: Most helpful
  1. answered 2021-03-19T08:12:48.557+00:00
    EchoLiu-MSFT 14,416 Reputation points

    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:
    79601-image.png

    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.

    No comments

  2. answered 2021-03-19T09:06:57.253+00:00
    Viorel 82,476 Reputation points

    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.

    No comments