Insert a Total Row Into a Dynamic Table For Reporting

Question

Post reply

Insert a Total Row Into a Dynamic Table For Reporting

tdanley

SSC-Addicted

Points: 473
More actions
August 2, 2013 at 7:58 am

#278312

I am creating a table based on a dynamic PIVOT. This table is used for reporting, i'm emailing out the results using DB send mail. I need to add a total row to this table BEFORE I email. Here is the code that creates the table.
-- Construct the full T-SQL statement
-- and execute dynamically
SET @sql = N'SELECT Customer, ' + @selectcols +' into DailySalesTotals
FROM (SELECT Customer, sorderdate, isnull(qty,0) as Quantity
FROM dbo.dailysales) AS D
PIVOT(sum(quantity) FOR sorderdate IN(' + @cols + N')) AS P order by 2 desc;';
EXEC sp_executesql @sql;
I've thought about then doing a
Insert into DailySalesTotals
Select 'Total', 0,0,0,0,0,0,0
but, then how to update the zeros to the column totals without knowing the column headers? Is there a way to loop and do that? Or reference ordinal position? Would love some help and suggestions! Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sturner SSC-Insane Points: 22411 More actions · Answer 1

insert the pivot into a temp table first, then select from that with you roll-up totals.

The probability of survival is inversely proportional to the angle of arrival.

tdanley SSC-Addicted Points: 473 More actions · Answer 2

sturner (8/2/2013)
insert the pivot into a temp table first, then select from that with you roll-up totals.

That still presents the same problem. Even if I put it in a temp table first, I have no idea what the column names are until the temp table is created. After I create that table how do I figure out what the column names are?

sturner SSC-Insane Points: 22411 More actions · Answer 3

Get the column names from sys.columns -- if it is a table it will have a row in sys.objects and one row for each column in sys.columns. Here's a query that I use:

select Case when ROW_NUMBER() over (order by column_id) > 1 then ',' else '' end+C.name

from sys.sysobjects O

join sys.columns C on O.id = C.[object_id]

where O.type = 'U' and O.name = '##zzz'

order by column_id

The probability of survival is inversely proportional to the angle of arrival.

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 4

tdanley (8/2/2013)
I am creating a table based on a dynamic PIVOT. This table is used for reporting, i'm emailing out the results using DB send mail. I need to add a total row to this table BEFORE I email. Here is the code that creates the table.
-- Construct the full T-SQL statement
-- and execute dynamically
SET @sql = N'SELECT Customer, ' + @selectcols +' into DailySalesTotals
FROM (SELECT Customer, sorderdate, isnull(qty,0) as Quantity
FROM dbo.dailysales) AS D
PIVOT(sum(quantity) FOR sorderdate IN(' + @cols + N')) AS P order by 2 desc;';
EXEC sp_executesql @sql;
I've thought about then doing a
Insert into DailySalesTotals
Select 'Total', 0,0,0,0,0,0,0
but, then how to update the zeros to the column totals without knowing the column headers? Is there a way to loop and do that? Or reference ordinal position? Would love some help and suggestions! Thanks!

Depending on how you're doing the dynamic pivot, calculating the total and maybe even some interim subtotals could be a piece o' cake. But you need to post your dynamic pivot code in order for me to figure that out for you.

--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)