Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, August 18, 2008 10:59 PM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 11:46 AM Points: 34,581, Visits: 28,767
Post #554742
 Posted Tuesday, August 19, 2008 1:41 AM
 Mr or Mrs. 500 Group: General Forum Members Last Login: Yesterday @ 5:18 AM Points: 551, Visits: 2,356
 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 TotalFROM #SomeTable1GROUP 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.02007   2.1     2.2     2.3     2.4     9.02008   1.5     0.0     2.3     1.9     5.7Sum    4.7     3.4     5.9     5.7    19.7*/ I love crosstabs and pivot-tables. Soothing, they are, like knitting. Best wishes,Phil FactorSimple Talk
Post #554784
 Posted Tuesday, August 19, 2008 3:55 AM
 SSC 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 Group: General Forum Members Last Login: Thursday, August 04, 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 Group: General Forum Members Last Login: Today @ 11:46 AM Points: 34,581, Visits: 28,767
 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." "Change is inevitable. Change for the better is not." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #554870
 Posted Tuesday, August 19, 2008 5:09 AM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 11:46 AM Points: 34,581, Visits: 28,767
 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.pjI 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." "Change is inevitable. Change for the better is not." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #554878
 Posted Tuesday, August 19, 2008 5:19 AM
 Forum Newbie Group: General Forum Members Last Login: Thursday, August 04, 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 Group: General Forum Members Last Login: Tuesday, November 26, 2013 5:03 AM Points: 2,365, Visits: 1,830
 gr8 article Jeff. Keep them coming. "Keep Trying"
Post #554895
 Posted Tuesday, August 19, 2008 6:29 AM
 Valued Member Group: General Forum Members Last Login: Monday, December 09, 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 Group: General Forum Members Last Login: Today @ 2:33 PM Points: 10,808, Visits: 12,332
 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 CorbettApplications Developer Don't let the good be the enemy of the best. -- Paul FlemingCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance ProblemsCrosstabs and Pivots or How to turn rows into columns Part 1Crosstabs and Pivots or How to turn rows into columns Part 2
Post #554952

 Permissions