PostgreSQL中将同ID行值转为新列的实现方案求助

你遇到的是典型的**行转列（Pivot）**需求，之前用CASE WHEN指定特定type值的方法不够通用，这里给你更灵活的实现思路——先给每个ID内的行分配序号，再通过条件聚合来生成目标列，不用绑定具体的type值。

核心思路 #

1. 给每个id下的记录分配行号（比如第1条、第2条），行号的排序规则可以根据你的需求调整（比如按type排序，或按原始数据顺序）。
2. 按id分组，用MAX()结合CASE WHEN根据行号提取对应行的type和name，生成type_1、name_1等列。

通用实现（支持MySQL 8.0+、PostgreSQL、SQL Server） #

这些数据库支持窗口函数ROW_NUMBER()，可以轻松生成行号：

SELECT
    id,
    MAX(CASE WHEN rn = 1 THEN type END) AS type_1,
    MAX(CASE WHEN rn = 1 THEN name END) AS name_1,
    MAX(CASE WHEN rn = 2 THEN type END) AS type_2,
    MAX(CASE WHEN rn = 2 THEN name END) AS name_2
FROM (
    SELECT
        id,
        type,
        name,
        -- 按type排序分配行号，可根据需求替换成其他字段（比如时间字段）
        ROW_NUMBER() OVER(PARTITION BY id ORDER BY type) AS rn
    FROM your_table_name -- 替换成你的表名
) t
GROUP BY id;

MySQL 5.x兼容方案（无窗口函数） #

如果你的MySQL版本不支持窗口函数，用用户变量生成行号：

SELECT
    id,
    MAX(CASE WHEN rn = 1 THEN type END) AS type_1,
    MAX(CASE WHEN rn = 1 THEN name END) AS name_1,
    MAX(CASE WHEN rn = 2 THEN type END) AS type_2,
    MAX(CASE WHEN rn = 2 THEN name END) AS name_2
FROM (
    SELECT
        id,
        type,
        name,
        @rn := IF(@prev_id = id, @rn + 1, 1) AS rn,
        @prev_id := id
    FROM your_table_name, (SELECT @prev_id := 0, @rn := 0) vars
    ORDER BY id, type -- 控制行号的排序规则
) t
GROUP BY id;

扩展说明 #

• 如果某个id的记录超过2条，只需继续添加MAX(CASE WHEN rn = 3 THEN type END) AS type_3这类语句即可，扩展性极强。
• 子查询里的ORDER BY决定了行号的顺序，比如你想让原始数据里先出现的记录对应type_1，如果表有插入时间字段，就用ORDER BY id, create_time替换ORDER BY id, type。

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