September 23, 2007 at 9:16 pm
I use the PIVOT clause to generate a pivot table. But i want to add a sum row at the end to show me the total result of each column, how can i do this?
Username Q1 Q2 Q3 Q4
User1 11 0 0 0
User2 1 0 0 0
User3 12 2 0 0
User4 8 1 0 0
31 3 0 0 <--- How can i add this line to the query results?
September 24, 2007 at 2:43 am
You can use UNION ALL to add the result of a query that returns the totals only.
You will probably want to order the whole union all statement if you want your result to be ordered.
Regards,
Andras
September 24, 2007 at 9:34 am
Andras is correct. And easy way to do this would be to throw your PIVOT into a CTE and then you can call it twice in the union.
______________________________________________________________________
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. SelburgSeptember 24, 2007 at 10:06 am
Performance-wise, you might care to revert back to the "case" syntax for creating your pivot table (i.e. the SQL 2000 "way" for pivoting tables) and adding in the WITH ROLLUP option instead. This allows for a single pass through the data, instead of 2 for the UNION ALL syntax.
Example:
select userid,
sum(case when qtr=1 then 1 else 0 end) as QTR1,
sum(case when qtr=2 then 1 else 0 end) as QTR2,
sum(case when qtr=3 then 1 else 0 end) as QTR2,
sum(case when qtr=4 then 1 else 0 end) as QTR2
from <mytable>
GROUP BY USERID with ROLLUP
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply