在数据透视表中添加总计 [英] Adding Grand Totals in Pivot Table

问题描述

我用以下代码创建了数据透视表:

I have 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)

但是我想做的是为每个PortfolioID添加总计，我不确定如何实现这一点.有帮助吗?

However what i would like to do is add grand totals for each portfolioID, and i'm unsure of how to achieve this. Any help?

推荐答案

您应该能够添加GROUP BY with ROLLUP来生成合计行，类似于:

You should be able to add a GROUP BY with ROLLUP to produce the totals row, similar to this:

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)

请参见带演示的SQL提琴. 注意:示例数据只是根据您的表结构来模拟的.

结果:

| DURATIONBAND | PORTFOLIO1 | PORTFOLIO2 | PORTFOLIO3 |
-------------------------------------------------------
|            2 |         78 |     (null) |     (null) |
|            5 |     (null) |     (null) |          4 |
|           12 |         10 |         45 |     (null) |
|        Total |         88 |         45 |          4 |

基于保持事物排序的需要，您可以使用:

Based in the need to keep things sorted, 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)

请参见带有演示的SQL提琴

这篇关于在数据透视表中添加总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！