SQL多行转多列求助：如何将同一REQUEST_ID的多行合并为一行

静态字段场景（固定FIELD_ID） #

如果你的FIELD_ID对应的字段是固定的（比如示例中的2-5对应四个固定标签），可以用条件聚合实现行转列，这是最直接高效的方法：

SELECT 
    r.REQUEST_ID,
    -- 提取Arrival Date相关字段
    MAX(CASE WHEN d.FIELD_ID = 2 THEN d.FIELD_ID END) AS arrival_field_id,
    MAX(CASE WHEN d.FIELD_ID = 2 THEN f.LABEL_en END) AS arrival_label,
    MAX(CASE WHEN d.FIELD_ID = 2 THEN d.DATA END) AS arrival_date,
    -- 提取Departure Date相关字段
    MAX(CASE WHEN d.FIELD_ID = 3 THEN d.FIELD_ID END) AS departure_field_id,
    MAX(CASE WHEN d.FIELD_ID = 3 THEN f.LABEL_en END) AS departure_label,
    MAX(CASE WHEN d.FIELD_ID = 3 THEN d.DATA END) AS departure_date,
    -- 提取Guest First Name相关字段
    MAX(CASE WHEN d.FIELD_ID = 4 THEN d.FIELD_ID END) AS guest_first_field_id,
    MAX(CASE WHEN d.FIELD_ID = 4 THEN f.LABEL_en END) AS guest_first_label,
    MAX(CASE WHEN d.FIELD_ID = 4 THEN d.DATA END) AS guest_first_name,
    -- 提取Guest Last Name相关字段
    MAX(CASE WHEN d.FIELD_ID = 5 THEN d.FIELD_ID END) AS guest_last_field_id,
    MAX(CASE WHEN d.FIELD_ID = 5 THEN f.LABEL_en END) AS guest_last_label,
    MAX(CASE WHEN d.FIELD_ID = 5 THEN d.DATA END) AS guest_last_name
FROM TABLE_requests r
INNER JOIN TABLE_fields_data d ON r.REQUEST_ID = d.REQUEST_ID
INNER JOIN TABLE_fields f ON f.FIELD_ID = d.FIELD_ID
GROUP BY r.REQUEST_ID;

关键逻辑说明 #

1. GROUP BY 聚合：通过GROUP BY r.REQUEST_ID将同一请求ID的所有行合并为一行
2. CASE WHEN 筛选：针对每个FIELD_ID，用CASE WHEN匹配对应的行，提取FIELD_ID、LABEL_en和DATA的值
3. MAX函数取值：因为每个请求ID对应每个FIELD_ID只有一条记录，MAX（或MIN）可以确保获取到唯一的非空值，避免聚合后出现NULL

查询结果示例 #

动态字段场景（FIELD_ID不固定） #

如果未来会新增FIELD_ID，静态的CASE WHEN需要手动修改，这时候可以用动态SQL生成查询语句，不同数据库的语法略有差异：

• MySQL：使用CONCAT拼接SQL，再通过PREPARE和EXECUTE执行
• SQL Server：使用STRING_AGG拼接字段，再通过EXEC执行
• PostgreSQL：使用STRING_AGG拼接，再通过EXECUTE执行

以MySQL为例，动态SQL示例：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN d.FIELD_ID = ', FIELD_ID, ' THEN d.FIELD_ID END) AS field_id_', FIELD_ID, ',',
      'MAX(CASE WHEN d.FIELD_ID = ', FIELD_ID, ' THEN f.LABEL_en END) AS label_', FIELD_ID, ',',
      'MAX(CASE WHEN d.FIELD_ID = ', FIELD_ID, ' THEN d.DATA END) AS data_', FIELD_ID
    )
  ) INTO @sql
FROM TABLE_fields;

SET @sql = CONCAT('SELECT r.REQUEST_ID, ', @sql, ' FROM TABLE_requests r INNER JOIN TABLE_fields_data d ON r.REQUEST_ID = d.REQUEST_ID INNER JOIN TABLE_fields f ON f.FIELD_ID = d.FIELD_ID GROUP BY r.REQUEST_ID');

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

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