row convert to column in sql 2008

Question

I want to convert a series of rows into a series of columns

create table #cusphone(cusid int,cusph1 int)
insert into #cusphone values(1,48509)
insert into #cusphone values(1,48508)
insert into #cusphone values(1,48507)
insert into #cusphone values(2,48100)

so that the output is like this

            1 48509 48508 48507
            2 48100  null  null   

Answer 1

You can use the same approach of rank() and then use the new PIVOT function as follows:

with    cusCte as(
select  cusid,cusph1,RANK() over (partition by cusid order by cusph1) r
from    #cusphone)

SELECT cusid, [1] AS C1, [2] AS C2, [3] AS C3
FROM 
(SELECT cusid,cusph1,r
FROM cusCte) p
PIVOT
(
MIN (cusph1)
FOR r IN
( [1], [2], [3] )
) AS pvt;