如何使用SQL将同组多行数据转换为单行多列格式？

针对同一CLASS分组的多条记录，将其转换为单行多列格式，每组对应ID、NAME、AGE字段，可通过窗口函数+条件聚合实现，具体方案如下：

1. 测试数据准备 #

先创建测试表并插入示例数据：

-- 创建测试表
CREATE TABLE student (
    CLASS VARCHAR(10),
    ID VARCHAR(10),
    NAME VARCHAR(20),
    AGE INT
);

-- 插入示例数据
INSERT INTO student (CLASS, ID, NAME, AGE)
VALUES 
('ST1', 'A1', 'ALI', 12),
('ST1', 'B1', 'MING', 13),
('ST1', 'C1', 'ABU', 12);

2. 核心转换SQL #

SELECT 
    CLASS,
    MAX(CASE WHEN rn = 1 THEN ID END) AS ID_1,
    MAX(CASE WHEN rn = 1 THEN NAME END) AS NAME_1,
    MAX(CASE WHEN rn = 1 THEN AGE END) AS AGE_1,
    MAX(CASE WHEN rn = 2 THEN ID END) AS ID_2,
    MAX(CASE WHEN rn = 2 THEN NAME END) AS NAME_2,
    MAX(CASE WHEN rn = 2 THEN AGE END) AS AGE_2,
    MAX(CASE WHEN rn = 3 THEN ID END) AS ID_3,
    MAX(CASE WHEN rn = 3 THEN NAME END) AS NAME_3,
    MAX(CASE WHEN rn = 3 THEN AGE END) AS AGE_3
FROM (
    SELECT 
        CLASS,
        ID,
        NAME,
        AGE,
        -- 给每个CLASS分组内的记录编号
        ROW_NUMBER() OVER (PARTITION BY CLASS ORDER BY ID) AS rn
    FROM student
) t
GROUP BY CLASS;

3. 逻辑说明 #

• 子查询中用ROW_NUMBER()按CLASS分组、ID排序，给每条记录分配唯一序号rn，确保同一分组内的记录可被区分；
• 外层通过MAX(CASE...)进行条件聚合：根据序号rn，将不同行的字段值映射到对应的列上，MAX用于过滤NULL值（每个序号仅对应一条有效记录，其余为NULL）。

4. 预期结果 #

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