Adding row and column totals to a dynamic pivot table
-
Hi there.
I'm new to pivot tables. I have the following pivot table and can't figure out how to add a totals row and a totals column. Any help would be greatly appreciated. Thanks:
SELECT *
FROM(
SELECT
YEAR(ActivityConsumedDate) [Year],
CASE MONTH(ActivityConsumedDate)
WHEN 1 THEN 'January'
WHEN 2 THEN 'February'
WHEN 3 THEN 'March'
WHEN 4 THEN 'April'
WHEN 5 THEN 'May'
WHEN 6 THEN 'June'
WHEN 7 THEN 'July'
WHEN 8 THEN 'August'
WHEN 9 THEN 'September'
WHEN 10 THEN 'October'
WHEN 11 THEN 'November'
WHEN 12 THEN 'December'
END as [Month],
Units
FROM _OFLENZ0001Activity INNER JOIN
_FFEmissionFactorGroup ON _OFLENZ0001Activity.EmissionFactorGroupID = _FFEmissionFactorGroup.EmissionFactorGroupID
WHERE (_OFLENZ0001Activity.EmissionFactorGroupID IN (16, 24, 25, 26)) and (_OFLENZ0001Activity.scope=1)
) WorkOrders
PIVOT
(
SUM(Units)
FOR [Month] IN (
[January],[February],[March],[April],
[May],[June],[July],[August],
[September],[October],[November],[December]
)
) AS PivotTable
ORDER BY [Year]
-
Since you're talking about a dynamic PIVOT I assume you're familiar with dynamic T-SQL syntax.
Therefore, I recommend to have a look at the DynamicCrossTab link referenced in my signature. It will most definitely answer your questions. And maybe you'll find the CrossTab syntax not only easier to remember than the Pivot syntax, it might also perform better...
-
Thanks Lutz. But for someone who is pretty new to this stuff there's a very large gap between my code and your article, especially if I need to modify the code further to work with multiple tables etc. Given this required learning curve and my time constraints do you have any suggestions given my original code?:unsure:
-
Chris Lindley (5/7/2010)
Thanks Lutz. But for someone who is pretty new to this stuff there's a very large gap between my code and your article, especially if I need to modify the code further to work with multiple tables etc. Given this required learning curve and my time constraints do you have any suggestions given my original code?:unsure:If we modifed the code to work as you desired, you would not be able to support it because you don't understand the content of the article. The article is simple... read the article. It even has examples that nearly fit your requirements exactly.
--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) -
Jeff Moden (5/8/2010)
...If we modifed the code to work as you desired, you would not be able to support it because you don't understand the content of the article. The article is simple... read the article. It even has examples that nearly fit your requirements exactly.
First thing to say is I'm not the author of the article. It's Jeff Moden, who just jumped in and told you exactly what I was thinking when reading your reply.
If you have serious time constraints and no time allowed for your learning curve I'd recommend you hire a consultant to support you.
I, personally, post on this forum because I'd like to share my (limited) knowledge and to try to help others learning how to write faster/more robust code. I'm not here to cover someones time constraints. That's a job for consultants.
So, please read the article, modify your code and get back here if you need further assitance.
-
Ok, I understand your code. But it's not as simple as that. I can't just go creating a tally table in the DB as I don't own it. After a quick chat with the admin he essentially wants me to write a business case for creating a tally table. We are a software as a service company with a multi tenanted db.
We have to decide how often the tally table is updated and from which tables, many of which have a high degree of inserts per second and the reports we provide our customers are meant to be in real-time.
Once we decide on how often the tally table is to be updated I also have to check your stated performance stats that take into account both the dynamic cross tab query and the tally table considering how often we will have to recreate the tally table given the fluidity of our data.
Then I have to consider the security implications as this is a multi-tenanted DB as I mentioned and different rows and fields are accessible to the user depending on the different application security roles (not DB security roles) they have.
So all in all your solution entails a significant process to be undergone which is why I was looking for a solution to my originally posted query.
But thanks all the same for your time. It's much appreciated. We will definitely be looking into the dynamic cross tab solution and will post our findings here.
-
Chris Lindley (5/8/2010)
Ok, I understand your code. But it's not as simple as that. I can't just go creating a tally table in the DB as I don't own it. After a quick chat with the admin he essentially wants me to write a business case for creating a tally table. We are a software as a service company with a multi tenanted db.We have to decide how often the tally table is updated and from which tables, many of which have a high degree of inserts per second and the reports we provide our customers are meant to be in real-time.
Once we decide on how often the tally table is to be updated I also have to check your stated performance stats that take into account both the dynamic cross tab query and the tally table considering how often we will have to recreate the tally table given the fluidity of our data.
Then I have to consider the security implications as this is a multi-tenanted DB as I mentioned and different rows and fields are accessible to the user depending on the different application security roles (not DB security roles) they have.
So all in all your solution entails a significant process to be undergone which is why I was looking for a solution to my originally posted query.
But thanks all the same for your time. It's much appreciated. We will definitely be looking into the dynamic cross tab solution and will post our findings here.
Heh... I've got a real appreciation for the bureaucratic BS that some folks will put you through. They just don't understand that 11,000 rows will dynamically handle 30 years of dates with no maintanence.
So, Old gaming trick... if you can't move, change color. Instead of creating a permanent Tally Table, create one on the fly and use it. The following code has a hell of a zing to it and doesn't stand the chance of someone accidently causing runaway code by forgetting to add the proper criteria because it's self limiting to only 10k rows...
--===== "Inline" CTE Driven "Tally Table” produces values up to-- 10,000... enough to cover VARCHAR(8000) and more than 27 years worth of datesWITHE1(N) AS ( --=== Create Ten 1'sSELECT 1 UNION ALL SELECT 1 UNION ALLSELECT 1 UNION ALL SELECT 1 UNION ALLSELECT 1 UNION ALL SELECT 1 UNION ALLSELECT 1 UNION ALL SELECT 1 UNION ALLSELECT 1 UNION ALL SELECT 1 --10),E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM E4)SELECT yada-yada-yadaFROM cteTally tWHERE t.N <= yada-yada-yadaWHERE yada-yada-yadaP.S. You need to get a new Admin... the one you have doesn't get it because a Tally table never needs to be updated.
I'm afraid that you don't get it either. Read the following article on the Tally table... maybe invite your Admin to do the same...
http://www.sqlservercentral.com/articles/T-SQL/62867/
--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) -
I wonder how you actually can provide really fast SAAS solutions without having a Tally table available
Would be interesting to have a small talk with your admin...
One thing I would ask is to count how often the following two words are used within the stored procs of one of your larger db's (not within comments): WHILE and CURSOR. I would expect a fairly large number...
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply