question

MalamMalam-4042 avatar image
0 Votes"
MalamMalam-4042 asked ·

Pivot Like Transact SQL

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
sql-server-transact-sql
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Do you have any update?
Please also remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Echo

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered ·

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.




image.png (5.3 KiB)
·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered ·

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.