SQL - Pivot with Grand Total Column and Row

 SQL - Pivot with Grand Total Column and Row

CREATE PROCEDURE [dbo].[PY_getPYdetails]                  
@pageIndex int  = null,                  
@pageSize int = null,                 
@EmployeeID int = null                  
AS                  
BEGIN 
DECLARE @columnHeaders NVARCHAR (MAX)
SELECT @columnHeaders  = COALESCE (@columnHeaders + ', [' + AdditionDescription + ']', '[' + AdditionDescription + ']')
FROM (select distinct AdditionDescription from PY_SalaryAdditions) as Pivt 
--print @columnHeaders
/* GRAND TOTAL COLUMN */
DECLARE @GrandTotalCol	NVARCHAR (MAX)
SELECT @GrandTotalCol = COALESCE (@GrandTotalCol + 'ISNULL([' + CAST (AdditionDescription AS VARCHAR) +'],0) + ', 'ISNULL([' + CAST(AdditionDescription AS VARCHAR)+ '],0) + ')
FROM (select distinct AdditionDescription from PY_SalaryAdditions) as Pivt ORDER BY AdditionDescription
SET @GrandTotalCol = LEFT (@GrandTotalCol, LEN(@GrandTotalCol)-1)
--print @GrandTotalCol
/* GRAND TOTAL ROW */
DECLARE @GrandTotalRow	NVARCHAR(MAX)
SELECT @GrandTotalRow = COALESCE(@GrandTotalRow + ',ISNULL(SUM([' + CAST(AdditionDescription AS VARCHAR)+']),0)', 'ISNULL(SUM([' + CAST(AdditionDescription AS VARCHAR)+']),0)')
FROM	(select distinct AdditionDescription from PY_SalaryAdditions) as Pivt ORDER BY AdditionDescription
--print @GrandTotalRow
/* MAIN QUERY */
DECLARE @FinalQuery NVARCHAR (MAX)
SET @FinalQuery = 	'SELECT *, ('+ @GrandTotalCol + ') AS [Total] INTO #temp_MatchesTotal
			FROM
				(
				Select ST.Payroll_ID [Period],SA.AdditionDescription,ST.Amount from PY_SalaryTransication ST inner join PY_SalaryAdditions SA  
				on SA.SalaryAdditionID =ST.SalaryAdditionID where  ST.SalaryAdditionID !=0 and ST.Status=1 and ST.EmployeeID='+cast(@EmployeeID AS VARCHAR)+'
				) A
			PIVOT
				(
				 SUM(Amount)
				 FOR AdditionDescription
				 IN ('+@columnHeaders +')
				) B
ORDER BY [Period] desc
SELECT * FROM #temp_MatchesTotal UNION ALL SELECT ''Total'','+@GrandTotalRow +', ISNULL(SUM([Total]),0) FROM #temp_MatchesTotal
DROP TABLE #temp_MatchesTotal'
--print @FinalQuery 
EXECUTE(@FinalQuery)

END