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

Insert a Total Row Into a Dynamic Table For Reporting Expand / Collapse
Author
Message
Posted Friday, August 2, 2013 7:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 6, 2013 12:19 PM
Points: 8, Visits: 7
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!
Post #1480414
Posted Friday, August 2, 2013 8:25 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 2:38 PM
Points: 1,426, Visits: 3,223
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.
Post #1480427
Posted Friday, August 2, 2013 8:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 6, 2013 12:19 PM
Points: 8, Visits: 7
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?
Post #1480438
Posted Friday, August 2, 2013 8:54 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 2:38 PM
Points: 1,426, Visits: 3,223
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.
Post #1480447
Posted Saturday, August 3, 2013 5:46 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 12:18 AM
Points: 36,751, Visits: 31,202
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."

(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 #1480743
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse