Post reply

Row transpose

  • March 25, 2010 at 6:35 am

    #219813

    Hi.

    I have id , vlaue pairs data in rows...what i need is to transpose them into columns without using pivot /unpivot..

    every set will have exactly 5 records..

    create table #rowtrans

    (id int,value varchar(10))

    insert into #rowtrans values(1,'a')

    insert into #rowtrans values(1,'b')

    insert into #rowtrans values(1,'c')

    insert into #rowtrans values(1,'d')

    insert into #rowtrans values(1,'e')

    insert into #rowtrans values(2,'a2')

    insert into #rowtrans values(2,'b2')

    insert into #rowtrans values(2,'c2')

    insert into #rowtrans values(2,'d2')

    insert into #rowtrans values(2,'e2')

    -- expected result is

    id col1 col2 col3 col4 col5

    1 a b c d e

    2 a2 b2 c2 d2 e2

    TIA

  • March 25, 2010 at 10:02 am

    #1139765

    How about this?

    ;With CTE as

    (Select *, Row_number() OVER (partition by id order by value) as row_no

    FROM #rowtrans)

    Select ID,

    max(CASe when Row_no = 1 THEN value ELSE '' END) as COL1,

    max(CASe when Row_no = 2 THEN value ELSE '' END) as COL2,

    max(CASe when Row_no = 3 THEN value ELSE '' END) as COL3,

    max(CASe when Row_no = 4 THEN value ELSE '' END) as COL4,

    max(CASe when Row_no = 5 THEN value ELSE '' END) as COL5

    From CTE

    Group by ID

    ---------------------------------------------------------------------------------

  • March 25, 2010 at 10:46 am

    #1139810

    Nabha, that looks like a winner to me.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • March 25, 2010 at 11:08 am

    #1139829

    The Dixie Flatline (3/25/2010)

    Nabha, that looks like a winner to me.

    Thank you 🙂

    ---------------------------------------------------------------------------------

  • March 25, 2010 at 11:25 pm

    #1140132

    Thats really cool..thanks a lot Nabha..

  • March 26, 2010 at 2:19 am

    #1140183

    Good code Nabha.. winner...

  • March 26, 2010 at 2:46 am

    #1140202

    Jus (3/25/2010)

    Thats really cool..thanks a lot Nabha..

    thanks and u r welcome.

    ColdCoffee

    Good code Nabha.. winner...

    Thanks mate.:-)

    ---------------------------------------------------------------------------------

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply