如何在SQL Pivot查询结果底部添加总计行？

现有以下SQL Pivot查询，可按State分组统计各Program的数量，需要在结果底部添加一行总计汇总各列数据：

SELECT * FROM 
(
    SELECT 
    State,
    --Program,
    Id,
    case 
        when program like '%Junior%' then 'Junior'
        when program like '%Senior%' then 'Senior'
        when program like '%Daughters and Dads%' then 'Daughters and Dads'
        when program like '%WWCF%' then 'WWCF'
        when program like '%Program%' then 'All Program'
    End as Program
    from table_1 
    where state is not null
) g
PIVOT (
count(Id) FOR 
    Program in ([Junior], [Senior], [Daughters and Dads], [CRICKET_BLAST], [WWCF], [All Program])
)AS pvt

方法一：用CTE+UNION ALL拼接总计行 #

通过CTE复用原查询逻辑，避免重复代码，再用UNION ALL拼接总计行：

WITH PivotData AS (
    SELECT 
        State,
        Id,
        case 
            when program like '%Junior%' then 'Junior'
            when program like '%Senior%' then 'Senior'
            when program like '%Daughters and Dads%' then 'Daughters and Dads'
            when program like '%WWCF%' then 'WWCF'
            when program like '%Program%' then 'All Program'
        End as Program
    from table_1 
    where state is not null
),
PivotedResults AS (
    SELECT 
        State,
        [Junior],
        [Senior],
        [Daughters and Dads],
        [CRICKET_BLAST],
        [WWCF],
        [All Program]
    FROM PivotData
    PIVOT (
        count(Id) FOR 
            Program in ([Junior], [Senior], [Daughters and Dads], [CRICKET_BLAST], [WWCF], [All Program])
    )AS pvt
)
-- 输出分组结果
SELECT * FROM PivotedResults
UNION ALL
-- 输出总计行
SELECT 
    '总计' AS State,
    SUM([Junior]),
    SUM([Senior]),
    SUM([Daughters and Dads]),
    SUM([CRICKET_BLAST]),
    SUM([WWCF]),
    SUM([All Program])
FROM PivotedResults;

方法二：在子查询中插入总计行后再Pivot #

在原始数据中额外添加一组State为'总计'的记录，统一进行Pivot操作：

SELECT * FROM 
(
    -- 原有按State分组的数据
    SELECT 
        State,
        Id,
        case 
            when program like '%Junior%' then 'Junior'
            when program like '%Senior%' then 'Senior'
            when program like '%Daughters and Dads%' then 'Daughters and Dads'
            when program like '%WWCF%' then 'WWCF'
            when program like '%Program%' then 'All Program'
        End as Program
    from table_1 
    where state is not null

    UNION ALL

    -- 用于生成总计行的原始数据，State固定为'总计'
    SELECT 
        '总计' AS State,
        Id,
        case 
            when program like '%Junior%' then 'Junior'
            when program like '%Senior%' then 'Senior'
            when program like '%Daughters and Dads%' then 'Daughters and Dads'
            when program like '%WWCF%' then 'WWCF'
            when program like '%Program%' then 'All Program'
        End as Program
    from table_1 
    where state is not null
) g
PIVOT (
    count(Id) FOR 
        Program in ([Junior], [Senior], [Daughters and Dads], [CRICKET_BLAST], [WWCF], [All Program])
)AS pvt

两种方法对比 #

• 方法一：仅扫描一次原始表，性能更优，代码结构清晰，适合数据量较大的场景；
• 方法二：代码更紧凑，但会额外扫描一次原始表，适合数据量较小的场景。

内容的提问来源于stack exchange，提问作者Akash Khare