未知列值时如何实现SQL透视(Pivot)及添加行总计与二维报表

基础数据与初始查询 #

给定Fruit表结构及数据：

id     food       color      price
-----  -------    -------    ----
1      cherry     red        0.23 
2      apple      red        0.65 
3      apple      green      0.77
4      orange     orange     1.03
5      lemon      yellow     1.45
6      grape      green      0.10
7      grape      purple     0.11
8      plum       purple     0.94

先执行分组统计，得到各颜色的数量：

SELECT color, count(id) AS 'tot' FROM Fruit GROUP BY color

执行结果：

color  tot
------ ---
green  2
orange 1
purple 2
red    2
yellow 1

基础透视实现（已知列值时）：

SELECT * FROM
( 
    SELECT color, count(id) AS 'tot' FROM Fruit GROUP BY color
) src
pivot
(
    SUM(tot)  -- 注意：原语句的SUM('tot')是错误写法，需去掉单引号
    FOR color IN ([green],[orange],[purple],[red],[yellow])
) piv

执行结果：

green orange purple red yellow
    2      1      2   2      1

问题1：为透视结果添加行总计 #

不需要使用UNPIVOT，直接在透视结果中计算各列总和即可：

SELECT 
    green,
    orange,
    purple,
    red,
    yellow,
    green + orange + purple + red + yellow AS total
FROM
( 
    SELECT color, count(id) AS 'tot' FROM Fruit GROUP BY color
) src
pivot
(
    SUM(tot)
    FOR color IN ([green],[orange],[purple],[red],[yellow])
) piv

执行结果：

green orange purple red yellow total
    2      1      2   2      1     8

问题2：生成带明细与总计的二维报表 #

先按food分组统计各颜色数量，通过透视转换为列，再用UNION ALL合并明细行和总计行：

-- 构建明细行与总计行的数据源
WITH FoodColorStats AS (
    SELECT 
        food,
        color,
        COUNT(id) AS cnt
    FROM Fruit
    GROUP BY food, color
    UNION ALL
    SELECT 
        'total' AS food,
        color,
        COUNT(id) AS cnt
    FROM Fruit
    GROUP BY color
)
-- 透视生成报表
SELECT 
    food,
    ISNULL(green, 0) AS green,
    ISNULL(orange, 0) AS orange,
    ISNULL(purple, 0) AS purple,
    ISNULL(red, 0) AS red,
    ISNULL(yellow, 0) AS yellow,
    ISNULL(green,0) + ISNULL(orange,0) + ISNULL(purple,0) + ISNULL(red,0) + ISNULL(yellow,0) AS total
FROM FoodColorStats
PIVOT (
    SUM(cnt)
    FOR color IN ([green],[orange],[purple],[red],[yellow])
) piv
ORDER BY 
    CASE WHEN food = 'total' THEN 1 ELSE 0 END,  -- 让总计行排在末尾
    food

执行结果：

food    green orange purple red yellow total
cherry     0      0      0   1      0     1
apple      1      0      0   1      0     2
orange     0      1      0   0      0     1
lemon      0      0      0   0      1     1
grape      1      0      1   0      0     2
plum       0      0      1   0      0     1
total      2      1      2   2      1     8

未知列值时的动态透视实现 #

当提前无法确定color的具体值时，需使用动态SQL拼接透视列，无需依赖带参数的存储过程（也可封装为存储过程），核心步骤：

1. 查询所有唯一color值，拼接成透视所需列列表
2. 动态生成并执行透视SQL语句

示例1：动态生成基础透视（带总计） #

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX)

-- 拼接透视列（带方括号避免关键字冲突）
SELECT @cols = STRING_AGG(QUOTENAME(color), ',')
FROM (SELECT DISTINCT color FROM Fruit) t

-- 生成动态透视SQL
SET @sql = N'
WITH ColorStats AS (
    SELECT color, COUNT(id) AS tot FROM Fruit GROUP BY color
)
SELECT 
    ' + @cols + N',
    (' + REPLACE(@cols, ',', ' + ') + N') AS total
FROM ColorStats
PIVOT (
    SUM(tot)
    FOR color IN (' + @cols + N')
) piv'

-- 执行动态SQL
EXEC sp_executesql @sql

示例2：动态生成二维报表（带明细与总计） #

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX), @sumCols NVARCHAR(MAX)

-- 拼接透视列
SELECT @cols = STRING_AGG(QUOTENAME(color), ',')
FROM (SELECT DISTINCT color FROM Fruit) t

-- 拼接总计列的求和表达式（处理NULL值）
SELECT @sumCols = STRING_AGG('ISNULL(' + QUOTENAME(color) + ',0)', ' + ')
FROM (SELECT DISTINCT color FROM Fruit) t

-- 生成动态报表SQL
SET @sql = N'
WITH FoodColorStats AS (
    SELECT 
        food,
        color,
        COUNT(id) AS cnt
    FROM Fruit
    GROUP BY food, color
    UNION ALL
    SELECT 
        ''total'' AS food,
        color,
        COUNT(id) AS cnt
    FROM Fruit
    GROUP BY color
)
SELECT 
    food,
    ' + REPLACE(@cols, ',', ', ISNULL(' + QUOTENAME(color) + ',0) AS ') + N',
    ' + @sumCols + N' AS total
FROM FoodColorStats
PIVOT (
    SUM(cnt)
    FOR color IN (' + @cols + N')
) piv
ORDER BY 
    CASE WHEN food = ''total'' THEN 1 ELSE 0 END,
    food'

EXEC sp_executesql @sql

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