CODEWITHSUNDEEP
sqlsql-server
Matt Gregory
Matt Gregory

Reputation: 8682

Pivot a table of phone numbers into Phone1, Phone2, etc. in SQL Server

I have a table like:

PhoneId  CustId     PhoneNum       Ext
   1       1        234-243-2345   100
   2       1        234-234-2456   101
   3       1        234-245-3253   NULL
   4       2        243-434-3553   NULL
   5       3        424-242-4242   NULL
   6       3        123-123-2322   333
   ...

How do I write a query to return a result like:

CustId    Phone1         Ext1   Phone2         Ext2    Phone3         Ext3
  1       234-243-2345   100    234-234-2456   101     234-245-3253   NULL
  2       243-434-3553   NULL   NULL           NULL    NULL           NULL
  3       424-242-4242   NULL   123-123-2322   333     NULL           NULL

Upvotes: 0

Views: 731

Answers (2)

Sagar
Sagar

Reputation: 275

Here is an alternate way of doing it :

See the demo here http://sqlfiddle.com/#!6/809b1/1

Select *
From (
Select CustId, value,col+'_'+
  cast(Row_Number()Over(Partition By custId, col Order by PhoneId) as nvarchar) col
From yourtable
Cross Apply
(
select PhoneNum, 'Phone' union all
select Ext, 'Ext'
) c(value, col) 
  ) X Pivot(max(Value) for 
            col in ([Phone_1],[Ext_1],[Phone_2],[Ext_2],[Phone_3],[Ext_3])) pvt

Upvotes: 1

radar
radar

Reputation: 13425

select custId,
max( case when T.seq =1 then T.PhoneNum end ) as 'Phone1',
max( case when T.seq =1 then T.Ext end ) as 'Ext1',
max( case when T.seq =2 then T.PhoneNum end ) as 'Phone2',
max( case when T.seq =2 then T.Ext end ) as 'Ext2',
max( case when T.seq =3 then T.PhoneNum end ) as 'Phone3',
max( case when T.seq =3 then T.Ext end ) as 'Ext3'
FROM
(select ROW_NUMBER() over ( partition by custId order by ( select null)) as seq, *  from Table1) T
group by custId

Upvotes: 1

Related Questions