MYSQL如何合并相同ID行并将对应数据存入新增自定义列

固定最多N行同ID场景（示例最多2行，直接使用以下语句即可） #

先通过窗口函数给同ID、同NAME的记录按顺序标记行号，再通过分组聚合提取对应行号的字段值：

SELECT
    ID,
    NAME,
    MAX(CASE WHEN row_num = 1 THEN VALUE END) AS `VALUE 1`,
    MAX(CASE WHEN row_num = 1 THEN DESCRIPTION END) AS `DESCRIPTION 1`,
    MAX(CASE WHEN row_num = 2 THEN VALUE END) AS `VALUE 2`,
    MAX(CASE WHEN row_num = 2 THEN DESCRIPTION END) AS `DESCRIPTION 2`
FROM (
    SELECT 
        ID,
        NAME,
        VALUE,
        DESCRIPTION,
        -- 同ID分组内按自定义排序规则生成行号，可调整ORDER BY后的字段适配实际需求
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY VALUE ASC) AS row_num
    FROM your_table
) AS t
GROUP BY ID, NAME;

语句说明 #

• 内层子查询用ROW_NUMBER()窗口函数，按ID分组后给每组内的行生成从1开始的序号，ORDER BY子句可根据实际排序需求调整，比如按数据插入时间、DESCRIPTION字段先后顺序等
• 外层查询按ID和NAME分组，通过CASE WHEN匹配对应行号的字段值，用MAX聚合函数把分散在多行的值合并到同一行，无匹配值的位置会自动留空

同ID行数不固定的动态场景 #

如果提前不知道同ID最多对应多少行，可使用动态SQL自动生成查询列：

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
    CONCAT(
        'MAX(CASE WHEN row_num = ', row_num, ' THEN VALUE END) AS `VALUE ', row_num, '`, ',
        'MAX(CASE WHEN row_num = ', row_num, ' THEN DESCRIPTION END) AS `DESCRIPTION ', row_num, '`'
    )
) INTO @sql
FROM (
    SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY VALUE ASC) AS row_num FROM your_table
) AS t;

SET @sql = CONCAT('SELECT ID, NAME, ', @sql, ' FROM (
    SELECT ID, NAME, VALUE, DESCRIPTION,
    ROW_NUMBER() OVER (PARTITION BY ID ORDER BY VALUE ASC) AS row_num
    FROM your_table
) AS t GROUP BY ID, NAME');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

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