Multiple pivot columns

  • Hi all,

    I have a query that displays a pivot column as follows. How can I add multiple pivot columns?

    Select * from (select * from tbl) p

    PIVOT(MAX(col1) FOR col2 IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS pvt

    I also want to add

    MAX(col3) FOR col2 IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])

    Thanks.

  • Hi all,

    I found the solution and it is as simple as(without any commas between pivots and the field names has to be unique):

    Select * from (select * from tbl) p

    PIVOT(MAX(col1) FOR col2 IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS pvt

    PIVOT(MAX(col3) FOR col2 IN ([21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],[32]))AS pvt1

    Thanks.

  • I replied too soon. I need the result in one record and that solution gives the result in multiple records. Any other solution?

  • Please read the article referenced in my signature. It will help us help you. 🙂

    ______________________________________________________________________

    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
  • Honestly, my best suggestion is don't use the T-SQL Pivot operator. Pivot in the presentation layer. Excel, SSRS, Crystal Reports, are all MUCH better at pivot table operations than T-SQL is.

    If you have to do it in the query for some reason (seriously, try to move it to a higher layer), then you'll need to build a custom pivot operation. You do that with Aggregate functions and Case statements.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • ...and if you decide to use SQL and not move this pivot to the presentation layer. What you would do is pivot with two seperate statements and join them. Either using inline queries, tmp table, etc.

    ______________________________________________________________________

    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
  • Hi Jason,

    I also thought of doing it in 2 selects and join the results, but wanted to try doing it in single select.

    Thanks for your time.

  • Please have a look at the CrossTab and DynamicCrossTab article referenced in my signature.

    Those might help you to find a solution.

    However, I second Gus: it should be done at the presentation layer, if possible.



    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]

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

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