如何在SQL中实现含重复Action值的数据透视表？

问题背景 #

需要将表中Action列的取值转为新列表头，用Val列数值填充，按ID分组。但同一ID下存在重复的Action值（如Check_X、Position），直接透视会导致数据冲突，需借助窗口函数处理。

原始表结构与数据 #

期望输出 #

解决方案 #

核心思路：先给每个ID内的重复Action记录分配行号，确保每组重复Action对应单独行，再基于ID和行号进行透视。

完整SQL代码 #

WITH numbered_data AS (
    SELECT
        Val,
        Action,
        ID,
        -- 给同一ID下的相同Action按出现顺序分配行号
        ROW_NUMBER() OVER (PARTITION BY ID, Action ORDER BY (SELECT NULL)) AS rn
    FROM your_table
)
SELECT
    MAX(CASE WHEN Action = 'SN' THEN Val END) AS SN,
    MAX(CASE WHEN Action = 'Check_X' THEN Val END) AS Check_X,
    MAX(CASE WHEN Action = 'Pass' THEN Val END) AS Pass,
    MAX(CASE WHEN Action = 'DateTime' THEN Val END) AS DateTime,
    MAX(CASE WHEN Action = 'Position' THEN Val END) AS Position
FROM numbered_data
GROUP BY ID, rn
ORDER BY SN, rn;

代码说明 #

1. 行号分配：用ROW_NUMBER()窗口函数，按ID和Action分组，给每个分组内的记录编号。ORDER BY (SELECT NULL)表示按默认顺序排序，可根据实际业务替换为真实排序字段（如时间列）。
2. 透视转换：用CASE语句将Action值转为列，通过MAX()聚合（也可用MIN()/ANY_VALUE()）获取对应Val。同一ID和行号下，每个Action只会有一个Val，聚合后即可得到正确值；非重复Action的Val会自动填充到同一ID的所有行中。

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