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!