如何在SQL中实现基于动态列数的Pivot透视表

需求说明 #

现有两张数据表：DefectiveTableList（存储所有缺陷分类）和SchoolDetail（存储各样本的缺陷评分），需创建动态列数的透视表：

• 列数由指定SchoolHeaderID对应的Sample列最大值决定（列名从0到该最大值）
• 行维度为DefectiveTableList中的DefectClassificationName
• 单元格填充SchoolDetail中对应缺陷分类、对应Sample的Score值，无数据则显示NULL

表结构 #

DefectiveTableList #

SysID  |    DefectClassificationName  | CreatedAt
-------|------------------------------|-------------
    4  |    Wrong Color               | 2022-08-26 16:31:20.470
    5  |    Paper Is Thin             | 2022-08-26 16:31:20.470
    6  |    Misaligned Print          | 2022-08-26 16:31:20.470
    7  |    Defective Device          | 2022-08-26 16:31:20.470
    8  |    Delamination              | 2022-08-26 16:31:20.470
    9  |    Burned Lamination         | 2022-08-26 16:31:20.470
   10  |    Cracked Box               | 2022-08-26 16:31:20.470
   11  |    Faded Color               | 2022-08-26 16:31:20.470
   12  |    Overlapping               | 2022-08-26 16:31:20.470

SchoolDetail #

ID  |   SchoolHeaderID | DefectClassification | Sample | Score
----|------------------|----------------------|--------|-------
   1|                 1| Overlapping          |       0|    3.0
   2|                 1| Delamination         |       0|    2.0
   5|                 1| Cracked Box          |       0|    1.5
   8|                 1| Wrong Color          |       1|    3.0
  13|                 3| Wrong Color          |       0|    3.0
  14|                 3| Burned Lamination    |       0|    1.0
  17|                 3| Misaligned Print     |       2|    1.5
  20|                 3| Paper Is Thin        |      10|    2.0
  23|                 3| Overlapping          |      11|    1.0

示例场景 #

当指定SchoolHeaderID=3时，Sample最大值为11，需生成0~11共12列，期望结果如下：

DefectClassificationName |  0 |  1 |  2 |  3 |  4 |  5 |  6 |  7 |  8 |  9 |  10 |  11 
-------------------------|----|----|----|----|----|----|----|----|----|----|-----|-----
Wrong Color              | 3.0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL| NULL| NULL
Paper Is Thin            |NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|  2.0| NULL
Misaligned Print         |NULL|NULL| 1.5|NULL|NULL|NULL|NULL|NULL|NULL|NULL| NULL| NULL
Defective Device         |NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL| NULL| NULL
Delamination             |NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL| NULL| NULL
Burned Lamination        | 1.0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL| NULL| NULL
Cracked Box              |NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL| NULL| NULL
Faded Color              |NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL| NULL| NULL
Overlapping              |NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL| NULL|  1.0

实现方案（动态SQL） #

由于列数是动态的（取决于指定SchoolHeaderID的Sample最大值），需使用动态SQL拼接透视表语句，完整代码如下：

-- 1. 定义变量：指定目标SchoolHeaderID、存储最大Sample值、动态列名
DECLARE @TargetSchoolHeaderID INT = 3;
DECLARE @MaxSample INT;
DECLARE @PivotColumns NVARCHAR(MAX);
DECLARE @DynamicSQL NVARCHAR(MAX);

-- 2. 获取指定SchoolHeaderID对应的Sample最大值
SELECT @MaxSample = MAX(Sample) 
FROM SchoolDetail 
WHERE SchoolHeaderID = @TargetSchoolHeaderID;

-- 3. 生成0到@MaxSample的所有列名（用于Pivot）
WITH Numbers AS (
    SELECT 0 AS Num
    UNION ALL
    SELECT Num + 1 
    FROM Numbers 
    WHERE Num < @MaxSample
)
SELECT @PivotColumns = STRING_AGG(QUOTENAME(Num), ', ')
FROM Numbers;

-- 4. 拼接动态SQL语句：先关联两张表，再执行Pivot
SET @DynamicSQL = N'
SELECT 
    DefectClassificationName, 
    ' + @PivotColumns + '
FROM (
    -- 关联缺陷分类表和评分表，确保所有缺陷分类都被包含（即使无评分）
    SELECT 
        dt.DefectClassificationName,
        sd.Sample,
        sd.Score
    FROM DefectiveTableList dt
    LEFT JOIN SchoolDetail sd 
        ON dt.DefectClassificationName = sd.DefectClassification
        AND sd.SchoolHeaderID = ' + CAST(@TargetSchoolHeaderID AS NVARCHAR) + '
) AS SourceData
PIVOT (
    MAX(Score) -- 用MAX聚合，因为每个(缺陷,Sample)组合只有一个Score
    FOR Sample IN (' + @PivotColumns + ')
) AS PivotTable
ORDER BY DefectClassificationName;';

-- 5. 执行动态SQL
EXEC sp_executesql @DynamicSQL;

代码说明 #

• 递归CTE生成连续数字：自动生成0到@MaxSample的所有整数，作为透视表的列名
• STRING_AGG拼接列名：将数字转为带引号的格式（如[0], [1]），符合Pivot语法要求
• LEFT JOIN关联表：保证DefectiveTableList中的所有缺陷分类都显示在结果中，避免遗漏无评分的分类
• 动态SQL执行：通过sp_executesql执行拼接好的语句，实现动态列数的需求

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