Post reply

Columns to rows

  • October 5, 2010 at 3:02 pm

    #229982

    Hi

    I have a table (T1) like this

    T1:

    Column1 column2 column3

    row11 row12 row13

    I want to get the result set as

    Result:

    T2:

    Column1 row11

    Column2 row12

    Column3 row13

    How canthis be done ?

    Thanks

  • October 5, 2010 at 3:06 pm

    #1232094

    That's called a PIVOT. Usually pivot operations are better done in excel or SSRS, but they are doable in t-sql.

    See here for the official doc: http://msdn.microsoft.com/en-us/library/ms177410.aspx

  • October 5, 2010 at 3:36 pm

    #1232110

    You might want to have a look at the CrossTab article referenced in my signature to see an alternative to PIVOT.

    I find it easier to remember and it seems to perform better.

    It's also easier to change it into DynamicCrossTab (see the related link in my sig).

    As a side note: I'm not the author of either of the articles I mentioned. I just find them most useful. 😉


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • October 5, 2010 at 4:42 pm

    #1232142

    Hey folks... look carefully. This is NOT a Pivot or CrossTab request. It's an UNpivot request.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 5, 2010 at 5:43 pm

    #1232166

    Here we go, Jeff:

    It is the CROSSTAB of UNPIVOTS

    Is this what we're looking for?

    declare @sample table (ID int identity(1,1), Column1 varchar(10), column2 varchar(10), column3 varchar(10))

    insert into @sample

    select 'row11', 'row12', 'row13' union all

    select 'row21', 'row22', 'row23'

    select * from @sample

    select ID,ca.*

    from @sample

    cross apply

    ( values

    ('column1', column1)

    ,('column2', column2)

    ,('column3', column3)

    ) CA ([column],[row])

    __________________________________________________

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

  • October 10, 2010 at 10:53 pm

    #1234781

    The Dixie Flatline (10/5/2010)

    Here we go, Jeff:

    It is the CROSSTAB of UNPIVOTS

    Is this what we're looking for?

    declare @sample table (ID int identity(1,1), Column1 varchar(10), column2 varchar(10), column3 varchar(10))

    insert into @sample

    select 'row11', 'row12', 'row13' union all

    select 'row21', 'row22', 'row23'

    select * from @sample

    select ID,ca.*

    from @sample

    cross apply

    ( values

    ('column1', column1)

    ,('column2', column2)

    ,('column3', column3)

    ) CA ([column],[row])

    That's pretty clever. I'll have to convert it to run in 2k5 just because I'm curious. Thanks, Bob.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 10, 2010 at 11:01 pm

    #1234782

    The Dixie Flatline (10/5/2010)

    Here we go, Jeff:

    It is the CROSSTAB of UNPIVOTS

    Is this what we're looking for?

    declare @sample table (ID int identity(1,1), Column1 varchar(10), column2 varchar(10), column3 varchar(10))

    insert into @sample

    select 'row11', 'row12', 'row13' union all

    select 'row21', 'row22', 'row23'

    select * from @sample

    select ID,ca.*

    from @sample

    cross apply

    ( values

    ('column1', column1)

    ,('column2', column2)

    ,('column3', column3)

    ) CA ([column],[row])

    Here's the 2K5 version of the unpivotting "cross tab" which I may use in 2K8 just so I only need to remember one way :-P. And, it looks like I have a new "toy" to play with. You can bet I'm going to do some testing for performance because this looks a WHOLE lot easier than an UnPivot to remember.

    SELECT orig.RowNum, unpvt.ColumnName, unpvt.Data

    FROM @Sample orig

    CROSS APPLY

    (

    SELECT 'Column1', Column1 UNION ALL

    SELECT 'Column2', Column2 UNION ALL

    SELECT 'Column3', Column3

    ) unpvt (ColumnName,Data)

    Thanks, Bob.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 11, 2010 at 8:12 am

    #1235013

    It not only reads easier, it should be child's play to generate dynamically as well.

    Jeff, you should check out this thread. It's about a year old, but it was one one of those great collaborations that pop up at SSC.

    http://www.sqlservercentral.com/Forums/Topic809640-338-1.aspx

    __________________________________________________

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

  • October 12, 2010 at 7:10 am

    #1235490

    The Dixie Flatline (10/11/2010)

    It not only reads easier, it should be child's play to generate dynamically as well.

    Jeff, you should check out this thread. It's about a year old, but it was one one of those great collaborations that pop up at SSC.

    http://www.sqlservercentral.com/Forums/Topic809640-338-1.aspx[/quote]

    Heh... yeah... "new toy". Not sure how I missed a thread like that one especially over the last year. At least great minds think alike on the 2k5 version. 😛

    I am going to practice what I preach, though... I'm going to do my own testing. First, that will help me memorize the method (although this one is incredibly easy). Second, it'll be fun for me. Third, I'll know for sure. 😀

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 12, 2010 at 8:31 am

    #1235576

    Geez Bob, I had forgotten all about that one. Thanks for the reminder, and for the opportunity to re-read "a great collaboration" again!

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 10 posts - 1 through 9 (of 9 total)

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