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 did not specify the rules by which something should appear in the first column vs the second column so I guessed that this is based on the occurrence (and thus sorting) of the cusph1 value.

With RankedItems As
    (
    Select cusid, cusph1
        , ROW_NUMBER() OVER( PARTITION BY cusid ORDER BY cusph1 DESC) As Num
    From #cusphone
    )
Select cusid
    , Min(Case When Num = 1 Then cusph1 End) As Col1
    , Min(Case When Num = 2 Then cusph1 End) As Col2
    , Min(Case When Num = 3 Then cusph1 End) As Col3
From RankedItems
Group By cusid