Post reply

Merge rows into columns

  • January 21, 2011 at 8:10 am

    #232523

    Hi

    I am trying to merge same table recursively to get some formatted output,

    For instance, I have table1 with following columns and data

    col1 col2 col3

    a 10 2

    a 20 3

    a 30 4

    b 5 1

    b 15 5

    c 50 8

    I want to transform above data into below format in table2

    col1 col2 col3 col2 col3 col2 col3

    a 10 2 20 3 30 4

    b 5 1 15 5 null null

    c 50 8 null null null null

    there are few constraints here, another combination of (a,40,1) can be added in table1 later.

    I tried with pivot, but it may not be useful because, i don't have to aggregate data in above table.

    Can someone help with a sample query.

    Thanks

    KJ

  • January 21, 2011 at 3:12 pm

    #1276830

    PIVOT won't work, because you need to pivot two columns (Col2 and Col3) and PIVOT will only work with one column. However, you are doing an aggregate, even though it does not look like it. You want to use Row_Number() partitioned by Col1 to create an ordering on the rows and then use that to create some CASE statements that will for each number that will output Col2 and Col3 when equal to the number and NULL otherwise and then do a MAX() or MIN() of each of those CASE statements. (It doesn't matter which you do, because there should only be on non-NULL value for each column for each value of Col1.)

    If you want tested code, I suggest you read the forum etiquette about posting data: http://qa.sqlservercentral.com/articles/61537/

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 2 posts - 1 through 1 (of 1 total)

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