行转列和列转行是 MySQL 中常见的数据格式转换场景，核心通过CASE WHEN、GROUP BY、CONCAT_WS、SUBSTRING_INDEX等函数实现，具体示例如下（基于以下测试表）：

测试表与初始数据

-- 行转列测试表（成绩表，每行是一个学生的一门成绩）
CREATE TABLE score (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(20), -- 学生姓名
    subject VARCHAR(20), -- 科目
    score INT -- 分数
);
INSERT INTO score (student_name, subject, score) VALUES
('张三', '语文', 90), ('张三', '数学', 85), ('张三', '英语', 95),
('李四', '语文', 88), ('李四', '数学', 92), ('李四', '英语', 80);

-- 列转行测试表（成绩表，每列是一门科目）
CREATE TABLE score_pivot (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(20), -- 学生姓名
    chinese INT, -- 语文
    math INT, -- 数学
    english INT -- 英语
);
INSERT INTO score_pivot (student_name, chinese, math, english) VALUES
('张三', 90, 85, 95), ('李四', 88, 92, 80);

1. 行转列（将多行数据转换为多列）

需求：将score表中每个学生的多门成绩（多行），转换为 “学生姓名 + 语文 + 数学 + 英语” 的一行数据。

实现方式：CASE WHEN + GROUP BY

SELECT
    student_name,
    -- 语文成绩：当科目为语文时，取分数，否则取0，分组后求和（实际只有一个非0值）
    SUM(CASE WHEN subject = '语文' THEN score ELSE 0 END) AS chinese,
    SUM(CASE WHEN subject = '数学' THEN score ELSE 0 END) AS math,
    SUM(CASE WHEN subject = '英语' THEN score ELSE 0 END) AS english
FROM score
GROUP BY student_name; -- 按学生分组，聚合各科成绩

结果：

student_name	chinese	math	english
张三	90	85	95
李四	88	92	80

说明：

• 若科目可能为空，可用IFNULL替换ELSE 0（如ELSE IFNULL(score, 0)）；
• 若科目较多，可结合动态 SQL 生成CASE WHEN语句（避免手动编写）。

2. 列转行（将多列数据转换为多行）

需求：将score_pivot表中每个学生的多门成绩（多列），转换为 “学生姓名 + 科目 + 分数” 的多行数据。

实现方式：UNION ALL + 列拆分

-- 语文成绩行
SELECT student_name, '语文' AS subject, chinese AS score FROM score_pivot
UNION ALL
-- 数学成绩行
SELECT student_name, '数学' AS subject, math AS score FROM score_pivot
UNION ALL
-- 英语成绩行
SELECT student_name, '英语' AS subject, english AS score FROM score_pivot;

结果：

student_name	subject	score
张三	语文	90
李四	语文	88
张三	数学	85
李四	数学	92
张三	英语	95
李四	英语	80

说明：

• UNION ALL保留重复行（适合无重复数据的场景），UNION去重（性能略低）；
• 若列较多，可通过动态 SQL 生成多个SELECT语句，再用UNION ALL拼接。

3. 扩展：动态行转列（科目不固定时）

若score表的科目不固定（如新增 “物理”“化学”），需动态生成列名，可通过 MySQL 的存储过程实现：

DELIMITER //
CREATE PROCEDURE dynamic_row_to_col()
BEGIN
    -- 1. 拼接科目列（如"MAX(CASE WHEN subject='语文' THEN score ELSE 0 END) AS 语文"）
    SET @col_sql = '';
    SELECT GROUP_CONCAT(
        CONCAT("MAX(CASE WHEN subject='", subject, "' THEN score ELSE 0 END) AS ", subject)
    ) INTO @col_sql FROM (SELECT DISTINCT subject FROM score) AS t;
    
    -- 2. 拼接完整SQL
    SET @full_sql = CONCAT("SELECT student_name, ", @col_sql, " FROM score GROUP BY student_name");
    
    -- 3. 执行动态SQL
    PREPARE stmt FROM @full_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

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

#