如何对含动态表头的数据进行Pivot行转列处理？

原始数据表格 #

需求描述 #

需要对上述表格执行Pivot操作：将HEADER2转为列、HEADER3转为行，且表头是动态变化的，不确定如何设置透视规则与执行行转列。尝试用CASE语句仅能得到单个表头对应的数据，现有代码如下：

DECLARE @SN INT
DECLARE @ST INT
DECLARE @SH INT
DECLARE @LTI INT 
DECLARE @LT INT
DECLARE @PI INT 


SET @SN = 0
SET @ST = 0
SET @SH = 0
SET @LTI = 0
SET @LT  = 0
SET @PI = 0

SELECT TOP 10000 MIN(SEQ_NO) AS 'SEQ_NO', MIN(SCRAP_TYPE) AS 'SCRAP_TYPE', MIN(SCRAP_HIST_ID) AS 'SCRAP_HIST_ID' , MIN(LOT_TRANS_ID) AS 'LOT_TRANS_ID' , MIN(LOT_NO) AS 'LOT_NO', MIN(PROC_ID) AS 'PROC_ID'
FROM ( SELECT CASE WHEN HEADER2 ='SEQ_NO' THEN 'SEQ_NO' END AS SEQ_NO
            , CASE WHEN HEADER2 ='SCRAP_TYPE' THEN 'SEQ_NO' END AS SCRAP_TYPE
            , CASE WHEN HEADER2 ='SCRAP_HIST_ID' THEN 'SEQ_NO' END AS SCRAP_HIST_ID
            , CASE WHEN HEADER2 ='LOT_TRANS_ID' THEN 'SEQ_NO' END AS LOT_TRANS_ID
            , CASE WHEN HEADER2 ='LOT_NO' THEN 'SEQ_NO' END AS LOT_NO  
            , CASE WHEN HEADER2 ='PROC_ID' THEN 'SEQ_NO' END AS PROC_ID
            , CASE WHEN HEADER2 = 'SEQ_NO' THEN                             @SN +1
                   WHEN HEADER2 = 'SCRAP_TYPE' THEN                         @ST+1
                   WHEN HEADER2 = 'SCRAP_HIST_ID' THEN @SH + 1
                   WHEN HEADER2 = 'LOT_TRANS_ID' THEN @LTI + 1
                   WHEN HEADER2 = 'LOT_NO' THEN @LT + 1
                   WHEN HEADER2 = 'PROC_ID' THEN @PI + 1 END AS ROWNUMBER
       FROM #temptable
       UNION ALL 
       SELECT CASE WHEN HEADER2 ='SEQ_NO' THEN HEADER3 END AS SEQ_NO
            , CASE WHEN HEADER2 ='SCRAP_TYPE' THEN HEADER3 END AS SCRAP_TYPE
            , CASE WHEN HEADER2 ='SCRAP_HIST_ID' THEN HEADER3 END AS SCRAP_HIST_ID
            , CASE WHEN HEADER2 ='LOT_TRANS_ID' THEN HEADER3 END AS LOT_TRANS_ID
            , CASE WHEN HEADER2 ='LOT_NO' THEN HEADER3 END AS LOT_NO  
            , CASE WHEN HEADER2 ='PROC_ID' THEN HEADER3 END AS PROC_ID
            , CASE WHEN HEADER2 = 'SEQ_NO' THEN @SN+1
                   WHEN HEADER2 = 'SCRAP_TYPE' THEN @ST+1
                   WHEN HEADER2 = 'SCRAP_HIST_ID' THEN @SH + 1
                   WHEN HEADER2 = 'LOT_TRANS_ID' THEN @LTI + 1
                   WHEN HEADER2 = 'LOT_NO' THEN @LT + 1
                   WHEN HEADER2 = 'PROC_ID' THEN @PI + 1 END AS ROWNUMBER
       FROM #temptable
       ) SUB

解决方案 #

因为表头是动态的，必须用动态SQL实现动态Pivot，具体步骤如下：

1. 分组标识
   给每组完整记录（比如你的数据每6条为一组）分配组ID，确保同一组的行能转成同一行的列：

SELECT 
    PK_ID,
    HEADER2,
    HEADER3,
    HEADER4,
    -- 每6条数据为一组生成组ID，若每组条数不固定需调整逻辑
    (ROW_NUMBER() OVER (ORDER BY PK_ID) - 1) / 6 AS GroupID
INTO #tempGrouped
FROM #temptable

2. 动态获取列名
   从HEADER2中提取所有唯一值，拼接成Pivot需要的列名格式：

DECLARE @cols NVARCHAR(MAX)
-- SQL Server 2017+ 用STRING_AGG
SELECT @cols = STRING_AGG(QUOTENAME(HEADER2), ', ')
FROM (SELECT DISTINCT HEADER2 FROM #tempGrouped) t

-- 低版本SQL Server替换为以下代码
-- SELECT @cols = STUFF((SELECT ', ' + QUOTENAME(HEADER2)
--                       FROM (SELECT DISTINCT HEADER2 FROM #tempGrouped) t
--                       FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

3. 构建并执行动态Pivot语句

DECLARE @sql NVARCHAR(MAX)
SET @sql = N'
SELECT GroupID, ' + @cols + N'
FROM #tempGrouped
PIVOT (
    MAX(HEADER3)
    FOR HEADER2 IN (' + @cols + N')
) AS pvt
ORDER BY GroupID'

EXEC sp_executesql @sql

说明 #

• 分组逻辑：如果每组的记录条数不是固定6条，可根据SEQ_NO出现的间隔或其他业务规则调整分组方式。
• Pivot聚合：用MAX(HEADER3)是因为每组中每个HEADER2只会出现一次，MAX等价于取唯一值，也可以用MIN，效果一致。

内容的提问来源于stack exchange，提问作者이정훈