如何用SQL合并学生表中重复字段的多行数据为单行多列？

完全可以用SQL实现这个需求，核心思路是分组聚合+条件判断（通用兼容多数数据库），或者利用数据库自带的行转列函数（比如PIVOT）。以下是具体实现方案：

原表结构与数据示例 #

Student_ID | Sex | Field_Of_Study | Department | Term | Subject | Grade_1 | Grade_2 | Grade_3
-----------|-----|----------------|------------|------|---------|---------|---------|--------
1          | M   | Comp_scie      | Inf        | 3    | Electr  | 2       | 3       | 0
1          | M   | Comp_scie      | Inf        | 3    | Python  | 4       | 0       | 0
1          | M   | Comp_scie      | Inf        | 3    | Java    | 3       | 4       | 0

目标表结构示例 #

Student_ID | Sex | Field_Of_Study | Department | Term | Subject_1 | Grade_11 | Grade_12 | Grade_13 | Subject_2 | Grade_21 | Grade_22 | Grade_23 | Subject_3 | Grade_31 | Grade_32 | Grade_33
-----------|-----|----------------|------------|------|-----------|----------|----------|----------|-----------|----------|----------|----------|-----------|----------|----------|----------
1          | M   | Comp_scie      | Inf        | 3    | Electr    | 2        | 3        | 0        | Python    | 4        | 0        | 0        | Java      | 3        | 4        | 0

通用SQL实现（兼容MySQL、PostgreSQL、SQL Server等） #

这种方法用窗口函数给每个分组内的科目编号，再通过条件聚合转成列，适配绝大多数数据库：

WITH ranked_scores AS (
    SELECT 
        Student_ID,
        Sex,
        Field_Of_Study,
        Department,
        Term,
        Subject,
        Grade_1,
        Grade_2,
        Grade_3,
        -- 给同一学生同一学期的科目分配唯一序号
        ROW_NUMBER() OVER (
            PARTITION BY Student_ID, Sex, Field_Of_Study, Department, Term
            ORDER BY Subject -- 可根据需求调整排序字段，比如按成绩、选课时间等
        ) AS subject_rank
    FROM student_scores -- 替换为你的表名
)
SELECT 
    Student_ID,
    Sex,
    Field_Of_Study,
    Department,
    Term,
    -- 提取第1门科目及对应成绩
    MAX(CASE WHEN subject_rank = 1 THEN Subject END) AS Subject_1,
    MAX(CASE WHEN subject_rank = 1 THEN Grade_1 END) AS Grade_11,
    MAX(CASE WHEN subject_rank = 1 THEN Grade_2 END) AS Grade_12,
    MAX(CASE WHEN subject_rank = 1 THEN Grade_3 END) AS Grade_13,
    -- 提取第2门科目及对应成绩
    MAX(CASE WHEN subject_rank = 2 THEN Subject END) AS Subject_2,
    MAX(CASE WHEN subject_rank = 2 THEN Grade_1 END) AS Grade_21,
    MAX(CASE WHEN subject_rank = 2 THEN Grade_2 END) AS Grade_22,
    MAX(CASE WHEN subject_rank = 2 THEN Grade_3 END) AS Grade_23,
    -- 提取第3门科目及对应成绩
    MAX(CASE WHEN subject_rank = 3 THEN Subject END) AS Subject_3,
    MAX(CASE WHEN subject_rank = 3 THEN Grade_1 END) AS Grade_31,
    MAX(CASE WHEN subject_rank = 3 THEN Grade_2 END) AS Grade_32,
    MAX(CASE WHEN subject_rank = 3 THEN Grade_3 END) AS Grade_33
FROM ranked_scores
GROUP BY Student_ID, Sex, Field_Of_Study, Department, Term;

代码说明 #

1. CTE部分：用ROW_NUMBER()窗口函数，按Student_ID、Sex、Field_Of_Study、Department、Term分组，给每个分组内的科目编号（1、2、3...），确保同一学生同一学期的每门科目有唯一标识。
2. 聚合部分：通过CASE判断科目序号，配合MAX函数将不同序号的科目和成绩分别转成对应的列，最后按分组字段聚合得到单行数据。

适配动态科目数量的扩展方案 #

如果学生的科目数量不固定（比如有的学生选2门，有的选5门），静态SQL需要预先知道最大科目数。如果需要自动适配任意数量的科目，可以用动态SQL生成查询语句：

以MySQL为例，编写存储过程生成动态SQL：

DELIMITER //
CREATE PROCEDURE pivot_student_scores()
BEGIN
    DECLARE max_rank INT;
    DECLARE sql_query TEXT DEFAULT '';
    DECLARE i INT DEFAULT 1;

    -- 获取最大科目数
    SELECT MAX(subject_rank) INTO max_rank
    FROM (
        SELECT ROW_NUMBER() OVER (
            PARTITION BY Student_ID, Sex, Field_Of_Study, Department, Term
            ORDER BY Subject
        ) AS subject_rank
        FROM student_scores
    ) AS ranks;

    -- 生成科目和成绩列的SQL片段
    WHILE i <= max_rank DO
        SET sql_query = CONCAT(sql_query,
            ', MAX(CASE WHEN subject_rank = ', i, ' THEN Subject END) AS Subject_', i,
            ', MAX(CASE WHEN subject_rank = ', i, ' THEN Grade_1 END) AS Grade_', i, '1',
            ', MAX(CASE WHEN subject_rank = ', i, ' THEN Grade_2 END) AS Grade_', i, '2',
            ', MAX(CASE WHEN subject_rank = ', i, ' THEN Grade_3 END) AS Grade_', i, '3'
        );
        SET i = i + 1;
    END WHILE;

    -- 拼接完整SQL并执行
    SET sql_query = CONCAT(
        'WITH ranked_scores AS (
            SELECT 
                Student_ID, Sex, Field_Of_Study, Department, Term,
                Subject, Grade_1, Grade_2, Grade_3,
                ROW_NUMBER() OVER (
                    PARTITION BY Student_ID, Sex, Field_Of_Study, Department, Term
                    ORDER BY Subject
                ) AS subject_rank
            FROM student_scores
        )
        SELECT Student_ID, Sex, Field_Of_Study, Department, Term',
        sql_query,
        ' FROM ranked_scores GROUP BY Student_ID, Sex, Field_Of_Study, Department, Term'
    );

    PREPARE stmt FROM sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

-- 调用存储过程
CALL pivot_student_scores();

这个存储过程会自动统计所有学生的最大科目数，生成对应的列，无需手动修改SQL。

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