SQL中如何将同ID多行合并为一行并生成新列？

现有表结构及数据：

+----+------+------+
| ID |  A   |  B   |
+----+------+------+
|  1 | 232  | 34   |
|  1 | 456  | 1234 |
|  2 | 786  | 38   |
|  2 | 567  | 456  |
|  2 | 2    | 5689 |
|  3 | 5    | 234  |
+----+------+------+

期望输出：

+----+-----+------+------+------+-----+-------+
| ID |  A  |  B   |A2    |  B2  |  A3  |  B3  |
+----+-----+------+------+------+------+------+
|  1 | 232 | 34   | 456  | 1234 | NULL | NULL | 
|  2 | 786 | 38   | 567  | 456  | 2    | 5689 |
|  3 |   5 | 234  | NULL | NULL | NULL | NULL |
+----+-----+------+------+-----+------+-------+

通用解法：条件聚合 + 行号标记 #

该方法适配绝大多数SQL数据库（MySQL、PostgreSQL、SQL Server等），核心逻辑是先给每个ID分组内的行分配序号，再通过条件判断将不同行的A、B值映射到对应列。

1. 生成分组内的行号

SELECT 
    ID,
    A,
    B,
    ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT 1)) AS row_num
FROM your_table;

ORDER BY (SELECT 1)用于保留原有数据顺序，若需特定排序规则，可替换为实际列（如ORDER BY A）。

2. 条件聚合生成目标结果

SELECT
    ID,
    MAX(CASE WHEN row_num = 1 THEN A END) AS A,
    MAX(CASE WHEN row_num = 1 THEN B END) AS B,
    MAX(CASE WHEN row_num = 2 THEN A END) AS A2,
    MAX(CASE WHEN row_num = 2 THEN B END) AS B2,
    MAX(CASE WHEN row_num = 3 THEN A END) AS A3,
    MAX(CASE WHEN row_num = 3 THEN B END) AS B3
    -- 若ID下行数超过3，继续添加row_num=4、5...的CASE语句即可
FROM (
    SELECT 
        ID,
        A,
        B,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT 1)) AS row_num
    FROM your_table
) t
GROUP BY ID
ORDER BY ID;

SQL Server 专属解法：PIVOT 运算符 #

利用PIVOT简化转置操作，需先将A、B列拆分为键值对，再进行转置：

WITH numbered_rows AS (
    SELECT 
        ID,
        A,
        B,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT 1)) AS row_num
    FROM your_table
),
unpivoted AS (
    SELECT
        ID,
        CONCAT(col, row_num) AS new_col,
        value
    FROM numbered_rows
    UNPIVOT (
        value FOR col IN (A, B)
    ) up
)
SELECT
    ID,
    A1 AS A,
    B1 AS B,
    A2,
    B2,
    A3,
    B3
FROM unpivoted
PIVOT (
    MAX(value) FOR new_col IN (A1, B1, A2, B2, A3, B3)
) pvt
ORDER BY ID;

旧版MySQL兼容方案（无窗口函数） #

若使用MySQL 8.0之前版本，用变量生成行号：

SELECT
    ID,
    MAX(CASE WHEN row_num = 1 THEN A END) AS A,
    MAX(CASE WHEN row_num = 1 THEN B END) AS B,
    MAX(CASE WHEN row_num = 2 THEN A END) AS A2,
    MAX(CASE WHEN row_num = 2 THEN B END) AS B2,
    MAX(CASE WHEN row_num = 3 THEN A END) AS A3,
    MAX(CASE WHEN row_num = 3 THEN B END) AS B3
FROM (
    SELECT 
        ID,
        A,
        B,
        @row_num := IF(@prev_id = ID, @row_num + 1, 1) AS row_num,
        @prev_id := ID
    FROM your_table, (SELECT @row_num := 0, @prev_id := NULL) vars
    ORDER BY ID
) t
GROUP BY ID
ORDER BY ID;

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