Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Expand / Collapse
Author
Message
Posted Monday, August 18, 2008 10:59 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:19 PM
Points: 36,724, Visits: 31,173
Comments posted to this topic are about the item Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

Folks, Part 2 of this article can be found at the following link...

(click here) Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #554742
Posted Tuesday, August 19, 2008 1:41 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 11:08 AM
Points: 577, Visits: 2,502

I hope I'm not mentioning anything that you are covering in subsequent articles, but when you need row and column totals, averages, or other aggregations, then suddenly the CUBE and ROLLUP operators start to become very useful. To take your example....



SELECT
  
COALESCE(CONVERT(CHAR(4),YEAR),'Sum'),
  
STR(SUM(CASE WHEN Quarter = 1 THEN Amount ELSE 0 END),5,1) AS [1st Qtr],
  
STR(SUM(CASE WHEN Quarter = 2 THEN Amount ELSE 0 END),5,1) AS [2nd Qtr],
  
STR(SUM(CASE WHEN Quarter = 3 THEN Amount ELSE 0 END),5,1) AS [3rd Qtr],
  
STR(SUM(CASE WHEN Quarter = 4 THEN Amount ELSE 0 END),5,1) AS [4th Qtr],
        
STR(SUM(Amount),5,1) AS Total
FROM #SomeTable1
GROUP BY YEAR WITH ROLLUP ORDER BY GROUPING(YEAR)

/*
     1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Total
---- ------- ------- ------- ------- -----
2006   1.1     1.2     1.3     1.4     5.0
2007   2.1     2.2     2.3     2.4     9.0
2008   1.5     0.0     2.3     1.9     5.7
Sum    4.7     3.4     5.9     5.7    19.7
*/

I love crosstabs and pivot-tables. Soothing, they are, like knitting.




Best wishes,

Phil Factor
Simple Talk
Post #554784
Posted Tuesday, August 19, 2008 3:55 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, November 25, 2009 8:51 AM
Points: 77, Visits: 63
That's interesting. I had my doubts as to how useful pivot might be. It's good to see it backed up by examples and statistics.
I'm looking forward to what you have to say about dynamic cross tabs. I recently had to do a cross tab for an electronic voting system using proportional representation where each election can have a different number of candidates. The only solution I could come up with in the time available works but is so complicated as to be ridiculous.
Post #554831
Posted Tuesday, August 19, 2008 5:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 4, 2011 1:56 AM
Points: 2, Visits: 15
Jeff Moden (8/18/2008)
Comments posted to this topic are about the item

What a performance! Access SQL had the Pivot command from 1992, it's taken 13 years for SQL proper to catch up. About to convert an Access db to a SQL Express back-end. I imagine the X-tab queries will be a major nightmare.
pj
Post #554865
Posted Tuesday, August 19, 2008 5:03 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:19 PM
Points: 36,724, Visits: 31,173
Phil Factor (8/19/2008)

I hope I'm not mentioning anything that you are covering in subsequent articles, but when you need row and column totals, averages, or other aggregations, then suddenly the CUBE and ROLLUP operators start to become very useful. To take your example....



Actually, you did, Phil... I'm devestated... ;)

True enough... that will be in a subsequent article, but you've stolen no thunder. Thank you for the feedback ol' friend.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #554870
Posted Tuesday, August 19, 2008 5:09 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:19 PM
Points: 36,724, Visits: 31,173
Peter Smith (8/19/2008)
Jeff Moden (8/18/2008)
Comments posted to this topic are about the item

What a performance! Access SQL had the Pivot command from 1992, it's taken 13 years for SQL proper to catch up. About to convert an Access db to a SQL Express back-end. I imagine the X-tab queries will be a major nightmare.
pj


I agree... thanks for the feedback, Peter. You'd think that if MS wanted to make it easier, they'd have come up with something a lot easier than Pivot syntax. What would be interesting would be to do a million row test on manual cross-tab vs the automatic cross-tab functionality in Access. If it's done right, the automatic cross-tab should should win by a significant margin.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #554878
Posted Tuesday, August 19, 2008 5:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 4, 2011 1:56 AM
Points: 2, Visits: 15
Jeff, I've copied your code. Maybe it will reduce the bad language as the upscaling to SQL takes place. Thanks for the article.
Post #554888
Posted Tuesday, August 19, 2008 5:24 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:53 AM
Points: 2,366, Visits: 1,844
gr8 article Jeff. Keep them coming.

"Keep Trying"
Post #554895
Posted Tuesday, August 19, 2008 6:29 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 9, 2013 1:26 PM
Points: 60, Visits: 258
That was an amazing article. Thanks so much, Jeff.

Paul DB
Post #554947
Posted Tuesday, August 19, 2008 6:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:34 AM
Points: 11,157, Visits: 12,898
Hey Jeff, good article. It's funny I have answered several forum questions recently using a Case embedded in an aggregate. They weren't crosstabs exactly, but similar. So, after having answered these questions I decided to post it on my blog, hoping someone might stumble upon it on a search. I was even considering submitting an article to SSC on it, but you beat me to the punch. At least the info is out there and with much more performance testing done on it than I would have done. Looking forward to the rest of the series.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #554952
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse