Post reply

Row transpose

  • April 29, 2010 at 3:31 am

    #220095

    Experts..

    i have name,value pairs of data in a table. ie data is in rows. can this be transposed to columns. each set will have 3 pairs of data (fixed).

    if object_id('tempdb..#test1') is not null

    drop table #test1

    create table #test1 (id1 int,period1 int,pname1 varchar(10))

    insert into #test1

    select 1,1,'a'

    union all

    select 1,2,'b'

    union all

    select 1,3,'c'

    union all

    select 2,4,'d'

    union all

    select 2,5,'e'

    union all

    select 2,6,'f'

    Expected output is:

    id1 period1 pname1 period2 pname2 period3 pname3

    1 1 a 2 b 3 c

    2 4 d 5 e 6 f

    TIA...

    -----------------------------------------------------------------------
    For better assistance in answering your questions[/url]
    Perforamance Issues[/url]
    Cross Tabs and Pivots[/url]
    Cross Apply[/url]
    The Numbers or Tally Table- Jeff Moden[/url]

  • April 29, 2010 at 4:49 am

    #1159542

    Beuatifully asked question and i-hope-it-is-beautiful code 😀

    Here you go

    ;WITH perid_CTE (ID, c1, c2, c3) AS

    (

    SELECT ID1, [1] PERIOD1,[2] PERIOD2,[3] PERIOD3 FROM

    (SELECT ID1, PERIOD1, ROW_NUMBER() OVER(PARTITION BY ID1 ORDER BY period1 ) R From #test1 ) PIVOT_TABLE

    PIVOT

    (MAX(PERIOD1) FOR R IN ([1],[2],[3])) PIVOT_HANDLE

    )

    , pname_CTE (ID, c1, c2, c3) AS

    (

    SELECT ID1, [1] pname1,[2] pname2,[3] pname3 FROM

    (SELECT ID1, pname1, ROW_NUMBER() OVER(PARTITION BY ID1 ORDER BY pname1 ) R From #test1 ) PIVOT_TABLE

    PIVOT

    (MAX(pname1) FOR R IN ([1],[2],[3])) PIVOT_HANDLE

    )

    SELECT P.ID , P.C1 period1 , N.C1 pname1, P.C2 period2 , N.C2 pname2, P.C3 period3,N.C3 pname3

    FROM

    perid_CTE P

    INNER JOIN

    pname_CTE N

    ON P.ID = N.ID

    Tell us if thats waht you wanted!

    Cheers!

  • April 29, 2010 at 5:17 am

    #1159554

    Wonderful coldcofee..you deserve a great Applause...

    -----------------------------------------------------------------------
    For better assistance in answering your questions[/url]
    Perforamance Issues[/url]
    Cross Tabs and Pivots[/url]
    Cross Apply[/url]
    The Numbers or Tally Table- Jeff Moden[/url]

  • April 29, 2010 at 5:21 am

    #1159559

    Thanks, NewBeeSQL

  • April 29, 2010 at 5:53 am

    #1159578

    Another variation of the same functionality but with much faster code

    ;WITH ORDERED_TABLE (ID, period1, R1, pname1, R2)

    AS

    (SELECT ID1

    ,period1, ROW_NUMBER() OVER(PARTITION BY ID1 ORDER BY period1) R1

    ,pname1 , ROW_NUMBER() OVER(PARTITION BY ID1 ORDER BY pname1 ) R2

    FROM #test1

    )

    SELECT

    ID,

    MAX(CASE R1 WHEN 1 THEN period1 END) AS period1,

    MAX(CASE R2 WHEN 1 THEN pname1 END) AS pname1,

    MAX(CASE R1 WHEN 2 THEN period1 END) AS period2,

    MAX(CASE R2 WHEN 2 THEN pname1 END) AS pname2,

    MAX(CASE R1 WHEN 3 THEN period1 END) AS period3,

    MAX(CASE R2 WHEN 3 THEN pname1 END) AS pname3

    FROM

    ORDERED_TABLE

    GROUP BY

    ID

    Try this also and use the most appropriate one for your need 🙂

  • April 30, 2010 at 12:48 am

    #1160235

    Second one is so sweet and fast as you....thanks again......

    -----------------------------------------------------------------------
    For better assistance in answering your questions[/url]
    Perforamance Issues[/url]
    Cross Tabs and Pivots[/url]
    Cross Apply[/url]
    The Numbers or Tally Table- Jeff Moden[/url]

  • April 30, 2010 at 2:56 am

    #1160264

    NewBeeSQL (4/30/2010)

    Second one is so sweet and fast as you....thanks again......

    Sheesh, NewBee..:blush: that one awesome appreciation.. i would say u are a Honey Bee.. lol...

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

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