Post reply

How to convert column result set in rows

  • January 9, 2008 at 12:08 pm

    #70692

    Hi,

    I have a script that bring output like this

    Name Code T1 T2 T3

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

    xyz 101 19 20 21

    I need my output to look like this

    Name Code Amt Type

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

    xyz 101 19 T1

    xyz 101 20 T2

    xyz 101 21 T3

    How to implement that?

    My script looks like this

    Select

    Name,

    Code,

    T1,

    T2,

    T3

    From

    .....................

  • January 9, 2008 at 12:14 pm

    #766363

    Read up on PIVOT and UNPIVOT in BOL. The answer is in there. 🙂

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

  • January 9, 2008 at 1:18 pm

    #766387

    Hi Jason,

    Does this Unpivot work for SQL 2000?

  • January 9, 2008 at 1:35 pm

    #766390

    No, Pivot, and Unpivot are new in sql2k5.

    Since you posted in 2k5 forum we assumed that is what u were using.

  • January 9, 2008 at 1:40 pm

    #766395

    oops..i did not notice.

    But this script is for Sql 2000. Is there any alternative to perform this in 2000?

    There is a PIVOT table option in books online..but it does exactly the opposite of what i want

  • January 9, 2008 at 1:44 pm

    #766397

    Then unfortunately your choices get a bit harder.

    The manual way to do that would be something like:

    select name, code, T1 as Amount, 'T1' as type

    from mytable

    UNION ALL

    select name, code, T2 as Amount, 'T2' as type

    from mytable

    UNION ALL

    select name, code, T3 as Amount, 'T3' as type

    from mytable

    Of course - if you have a LOT of columns - that will get "old" quick, so you might care to investigate dynamic SQL to make that happen.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • January 10, 2008 at 7:21 am

    #766638

    Better than that, correctly designing a normalized table would go a long way 😉

    --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)

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

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