SQL Server 2014 透视表如何添加按月份统计的行、列总计与全局总计

实现方案 #

你可以通过CTE包裹原有透视逻辑计算行总计，再搭配ROLLUP分组生成列总计和全局总计，SQL Server 2014完全兼容该语法，完整代码如下：

WITH PivotResult AS (
    SELECT 
        [Year],
        ProductID,
        ISNULL([January],0) AS [January],
        ISNULL([February],0) AS [February],
        ISNULL([March],0) AS [March],
        ISNULL([April],0) AS [April],
        ISNULL([May],0) AS [May],
        ISNULL([June],0) AS [June],
        ISNULL([July],0) AS [July],
        ISNULL([August],0) AS [August],
        ISNULL([September],0) AS [September],
        ISNULL([October],0) AS [October],
        ISNULL([November],0) AS [November],
        ISNULL([December],0) AS [December],
        -- 行总计：当前行对应产品全年的汇总数量
        ISNULL([January],0) + ISNULL([February],0) + ISNULL([March],0) + ISNULL([April],0) +
        ISNULL([May],0) + ISNULL([June],0) + ISNULL([July],0) + ISNULL([August],0) +
        ISNULL([September],0) + ISNULL([October],0) + ISNULL([November],0) + ISNULL([December],0) AS RowTotal
    FROM(
        SELECT
            YEAR(DueDate) [Year],
            CASE MONTH(DueDate)
                WHEN 1 THEN 'January'
                WHEN 2 THEN 'February'
                WHEN 3 THEN 'March'
                WHEN 4 THEN 'April'
                WHEN 5 THEN 'May'
                WHEN 6 THEN 'June'
                WHEN 7 THEN 'July'
                WHEN 8 THEN 'August'
                WHEN 9 THEN 'September'
                WHEN 10 THEN 'October'
                WHEN 11 THEN 'November'
                WHEN 12 THEN 'December'
            END as [Month],
            ProductID,
            OrderQty
        FROM Production.WorkOrder
    ) WorkOrders
    PIVOT
    (
        SUM(OrderQty)
        FOR [Month] IN (
            [January],[February],[March],[April],
            [May],[June],[July],[August],
            [September],[October],[November],[December]
        )
    ) AS PivotTable
)
SELECT 
    CASE WHEN GROUPING([Year]) = 1 THEN '全局总计' ELSE CAST([Year] AS VARCHAR(10)) END AS [Year],
    CASE WHEN GROUPING(ProductID) = 1 THEN 
        CASE WHEN GROUPING([Year]) = 1 THEN '' ELSE '年度列总计' END 
        ELSE CAST(ProductID AS VARCHAR(20)) 
    END AS ProductID,
    SUM([January]) AS [January],
    SUM([February]) AS [February],
    SUM([March]) AS [March],
    SUM([April]) AS [April],
    SUM([May]) AS [May],
    SUM([June]) AS [June],
    SUM([July]) AS [July],
    SUM([August]) AS [August],
    SUM([September]) AS [September],
    SUM([October]) AS [October],
    SUM([November]) AS [November],
    SUM([December]) AS [December],
    SUM(RowTotal) AS RowTotal
FROM PivotResult
GROUP BY ROLLUP([Year], ProductID)
ORDER BY 
    GROUPING([Year]),
    [Year],
    GROUPING(ProductID),
    ProductID

逻辑说明 #

1. 先用ISNULL处理月份字段的空值，避免求和时出现NULL错误
2. 新增RowTotal字段累加单行列所有月份的数值，实现行总计
3. 用ROLLUP自动生成三级统计：原有产品明细行、单年度所有产品的列总计行、全维度的全局总计行
4. GROUPING()函数用于识别总计行，替换对应字段的展示文本，符合报表阅读习惯
5. 排序逻辑保证总计行展示在对应分组的末尾，也可自行调整排序规则修改位置

如果不需要分年度的列总计，仅需要全局列总计，把GROUP BY ROLLUP([Year], ProductID)改为GROUP BY GROUPING SETS (([Year],ProductID), ())即可。

内容的提问来源于stack exchange，提问作者Velvet Dreams