如何在无聚合且列数可变的情况下实现SQL数据透视？

需求说明 #

现有数据表包含以下字段：

• machineId：设备唯一标识，格式为AGR<类型>.<序列号>
• periodId：周期唯一标识（对应具体日期）
• errorId：错误唯一标识（如ERR561代表过热）

每个周期内同一设备若存在多个错误，会生成多条同周期记录。需要将表进行透视转换，输出每行对应一个machineId+periodId组合，错误码分别放入ERROR1、ERROR2等动态列中，无对应错误则填充NULL。

遇到的核心问题：

1. 常规透视多针对聚合场景（如求和、计数），此处无需要聚合的数值字段，仅需转置错误码
2. 每个周期的错误数量不固定（0到100个不等），无法提前固定列数

解决方案：动态SQL实现动态透视 #

步骤1：为每个设备周期内的错误添加序号 #

先通过窗口函数为每个machineId+periodId组内的错误生成唯一序号，作为后续透视的分组依据：

SELECT 
    machineId,
    periodId,
    errorId,
    'ERROR' + CAST(ROW_NUMBER() OVER(PARTITION BY machineId, periodId ORDER BY errorId) AS VARCHAR(3)) AS errorColName
FROM #sampleData

步骤2：动态生成透视列并执行透视 #

由于错误数量不固定，需先动态生成所有可能的错误列名，再拼接透视SQL执行：

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

-- 生成所有需要的透视列名（适配SQL Server 2017+）
SELECT @cols = STRING_AGG(QUOTENAME(errorColName), ', ')
FROM (
    SELECT DISTINCT 
        'ERROR' + CAST(ROW_NUMBER() OVER(PARTITION BY machineId, periodId ORDER BY errorId) AS VARCHAR(3)) AS errorColName
    FROM #sampleData
) t

-- 拼接动态透视SQL
SET @query = N'
SELECT machineId, periodId, ' + @cols + N'
FROM (
    SELECT 
        machineId,
        periodId,
        errorId,
        ''ERROR'' + CAST(ROW_NUMBER() OVER(PARTITION BY machineId, periodId ORDER BY errorId) AS VARCHAR(3)) AS errorColName
    FROM #sampleData
) src
PIVOT (
    MAX(errorId) FOR errorColName IN (' + @cols + N')
) pvt
ORDER BY machineId, periodId
'

-- 执行动态SQL
EXEC sp_executesql @query

关键说明 #

• 用ROW_NUMBER()给每个设备周期内的错误排序生成序号，解决无聚合字段的问题（透视时用MAX(errorId)，因每个序号对应唯一错误码，MAX/MIN效果一致）
• 利用STRING_AGG()动态生成所有透视列，自动适配错误数量变化的场景
• 执行后每行对应一个machineId+periodId组合，无错误的列自动填充NULL

示例输出片段 #

以AGR7.00012在periodId=9的记录为例，输出格式如下：

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