Adding grand totals to a pivot table
I created a pivot table with the following code:
DECLARE @SQL AS VARCHAR(MAX)
DECLARE @Columns AS VARCHAR (MAX)
SELECT @Columns =
COALESCE(@Columns + ', ','')+ QUOTENAME(PortfolioID)
FROM
(
SELECT PortfolioID FROM InfoPortal.DBO.fn_Generic_PortfolioGroupMembers (@PortfolioGroup)
) AS B
ORDER BY B.PortfolioID
SET @SQL = '
WITH PivotData AS
(
SELECT
PortfolioID, Percentage, DurationBand, DurationSort
FROM #Worktable
)
SELECT
DurationBand,
' + @Columns + '
FROM PivotData
PIVOT
(
SUM(Percentage)
FOR PortfolioID
IN (' + @Columns + ')
) AS PivotResult
ORDER BY DurationSort'
EXEC (@SQL)
However, what I would like to do is add grand totals for each portfolioID, and I'm not sure how to achieve this. Any help?
+1
source to share
1 answer
You can add GROUP BY with ROLLUP
to create a total line similar to the following:
DECLARE @SQL AS VARCHAR(MAX)
DECLARE @Columns AS VARCHAR (MAX)
DECLARE @ColumnsRollup AS VARCHAR (MAX)
SELECT @Columns =
COALESCE(@Columns + ', ','')+ QUOTENAME(PortfolioID)
FROM
(
SELECT distinct PortfolioID
FROM worktable
) AS B
ORDER BY B.PortfolioID
SELECT @ColumnsRollup =
COALESCE(@ColumnsRollup + ', Sum(','Sum(')+ QUOTENAME(cast(PortfolioID as varchar(10)))+') as Portfolio'+cast(PortfolioID as varchar(10))
FROM
(
SELECT distinct PortfolioID
FROM worktable
) AS B
ORDER BY B.PortfolioID
SET @SQL = '
WITH PivotData AS
(
SELECT PortfolioID, Percentage, DurationBand, DurationSort
FROM Worktable
)
SELECT case when DurationBand is not null then cast(durationband as varchar(10))
else ''Total'' end Durationband, ' + @ColumnsRollup+ '
FROM
(
SELECT DurationBand, ' + @Columns + '
FROM PivotData
PIVOT
(
SUM(Percentage)
FOR PortfolioID IN (' + @Columns + ')
) AS PivotResult
) src
GROUP BY DurationBand with ROLLUP'
EXEC (@SQL)
See SQL Fiddle with Demo . Note: the sample data is just mocked by your table structure.
Result:
| DURATIONBAND | PORTFOLIO1 | PORTFOLIO2 | PORTFOLIO3 |
-------------------------------------------------------
| 2 | 78 | (null) | (null) |
| 5 | (null) | (null) | 4 |
| 12 | 10 | 45 | (null) |
| Total | 88 | 45 | 4 |
Based on the need to sort things, you can use:
DECLARE @SQL AS VARCHAR(MAX)
DECLARE @Columns AS VARCHAR (MAX)
DECLARE @ColumnsRollup AS VARCHAR (MAX)
SELECT @Columns =
COALESCE(@Columns + ', ','')+ QUOTENAME(PortfolioID)
FROM
(
SELECT distinct PortfolioID
FROM worktable
) AS B
ORDER BY B.PortfolioID
SELECT @ColumnsRollup =
COALESCE(@ColumnsRollup + ', Sum(','Sum(')+ QUOTENAME(cast(PortfolioID as varchar(10)))+') as '+QUOTENAME(cast(PortfolioID as varchar(10)))
FROM
(
SELECT distinct PortfolioID
FROM worktable
) AS B
ORDER BY B.PortfolioID
SET @SQL = '
WITH PivotData AS
(
SELECT PortfolioID, Percentage, DurationBand, DurationSort
FROM Worktable
)
SELECT *
FROM
(
SELECT DurationBand, ' + @Columns + '
FROM PivotData
PIVOT
(
SUM(Percentage)
FOR PortfolioID IN (' + @Columns + ')
) AS PivotResult
UNION ALL
select ''Grand Total'', '+@ColumnsRollup+'
from
(
SELECT DurationBand, ' + @Columns + '
FROM PivotData
PIVOT
(
SUM(Percentage)
FOR PortfolioID IN (' + @Columns + ')
) AS PivotResult
)tot
) src
'
EXEC (@SQL)
+3
source to share