;WITH PIVOTCTE as (select QuoteDate as ActivityDate, Client, 'Quotes' as ActionType, NumQuotes as Qty FROM dbo.QuoteMaster UNION SELECT CancelDate as ActivityDate, Client, Problem as ActionType, Qty from dbo.CancellationMaster) , FinalPivotCTE as (SELECT ActivityDate, Client, [Quotes],[Declined at begining],[No Credit Card],[Not Ended] FROM PIVOTCTE pivot ( SUM(Qty) for ActionType in ([Quotes],[Declined at begining],[No Credit Card],[Not Ended])) TEMPLIST ) select * from FinalPivotCTE UNION ALL SELECT NULL as ActivityDate, NULL AS Client, SUM([Quotes]),SUM([Declined at begining]),SUM([No Credit Card]),SUM([Not Ended]) FROM FinalPivotCTEOK, now for modifying the original procedure to add to the generated code, to SUM all the dynamic columns, here's a new procedure that does everything the first one did. Note that in the code I've added an additional string that figures out what columns to SUM...
CREATE PROCEDURE dbo.DynamicPivotExampleWithTotals as begin DECLARE @ActionSelectString nvarchar(4000) , @SQLPivotQuery nvarchar(4000) ;WITH ListTempCTE as (SELECT 'Quotes' AS ActionType , 1 as OrderNum UNION SELECT DISTINCT Problem as Actiontype, 2 as OrderNum FROM CancellationMaster ) SELECT @ActionSelectString = stuff ( ( select ',[' + cast(ActionType as varchar(100)) + ']' from ListTempCTE ORDER BY OrderNum, ActionType for xml path('') ), 1, 1, '') DECLARE @SumString nvarchar(4000) SET @SumString = REPLACE( @ActionSelectString, '[', 'SUM([') SET @SumString = REPLACE( @SumString, ']', '])' ) SET @SQLPivotQuery = ';WITH PIVOTCTE as (select QuoteDate as ActivityDate, Client, ''Quotes'' as ActionType, NumQuotes as Qty FROM dbo.QuoteMaster UNION SELECT CancelDate as ActivityDate, Client, Problem as ActionType, Qty from dbo.CancellationMaster) , ' set @SQLPivotQuery = @SqlPivotQuery + ' FinalPivotCTE as (SELECT ActivityDate, Client, ' + @ActionSelectString + ' FROM PIVOTCTE pivot ( SUM(Qty) for ActionType in (' + @ActionSelectString + ')) TEMPLIST ) ' + char(13) + char(10) set @SQLPivotQuery = @SQLPivotQuery + 'select * from FinalPivotCTE UNION ALL SELECT NULL as ActivityDate, NULL AS Client, ' + @SumString + ' FROM FinalPivotCTE' EXEC sp_executesql @SqlPivotQuery endWonderful Kevin, thank you very much, this will help me to improve the code you gave me before. Sorry I didn't reply yesterday I got disconnected before you. Right now what I'm dealing with is this: