如何在SQL中实现数据透视：按3列分组处理大量ThemeID值

原始数据 #

目标结果 #

解决方案 #

方法一：条件聚合（通用所有SQL数据库）

先通过窗口函数ROW_NUMBER()给每个ID+Name分组内的ThemeID分配序号，再用条件聚合将多行转成固定列：

SELECT
    ID,
    Name,
    MAX(CASE WHEN rn = 1 THEN ThemeID END) AS ThemeID1,
    MAX(CASE WHEN rn = 2 THEN ThemeID END) AS ThemeID2,
    MAX(CASE WHEN rn = 3 THEN ThemeID END) AS ThemeID3
FROM (
    -- 子查询：给每个分组内的ThemeID编号
    SELECT
        ID,
        Name,
        ThemeID,
        ROW_NUMBER() OVER (PARTITION BY ID, Name ORDER BY ThemeID) AS rn
    FROM your_table_name -- 替换为实际表名或原始查询语句
) t
GROUP BY ID, Name;

方法二：使用PIVOT函数（以SQL Server为例）

如果你的数据库支持PIVOT语法，也可以用以下方式实现：

SELECT ID, Name, [1] AS ThemeID1, [2] AS ThemeID2, [3] AS ThemeID3
FROM (
    SELECT
        ID,
        Name,
        ThemeID,
        ROW_NUMBER() OVER (PARTITION BY ID, Name ORDER BY ThemeID) AS rn
    FROM your_table_name -- 替换为实际表名或原始查询语句
) t
PIVOT (
    MAX(ThemeID)
    FOR rn IN ([1], [2], [3]) -- 指定要转置的序号值
) p;

注意事项 #

• 替换语句中的your_table_name为实际表名，若原始数据来自查询结果，直接将子查询的FROM部分替换为你的查询即可
• ORDER BY ThemeID可根据需求调整排序规则（比如按插入时间、ThemeID大小等）
• 若某个ID对应的ThemeID不足3个，对应列会返回NULL，符合业务需求

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