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!