January 17, 2012 at 11:43 am
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.
January 17, 2012 at 1:10 pm
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.
January 17, 2012 at 1:23 pm
I replied too soon. I need the result in one record and that solution gives the result in multiple records. Any other solution?
January 17, 2012 at 1:29 pm
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. SelburgJanuary 17, 2012 at 1:40 pm
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
January 17, 2012 at 1:58 pm
...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. SelburgJanuary 17, 2012 at 2:20 pm
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.
January 17, 2012 at 4:49 pm
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply