行列转换-横表竖表互相转换

一、多行转多列（竖表转横表）

原始数据中是一个竖表，每个学生的每个学科一行数据，对其转换成一张横表，即表中学生id为主键，包含语文、数学、英语三列，列值为对应学科分数。

行转列-基础数据

有学生成绩表，包含学生id、学科、成绩

+-------------+----------+--------+
| student_id  | subject  | score  |
+-------------+----------+--------+
| 001         | 语文       | 89     |
| 001         | 数学       | 95     |
| 001         | 英语       | 77     |
| 002         | 语文       | 92     |
| 002         | 数学       | 83     |
| 002         | 英语       | 97     |
| 003         | 语文       | 81     |
| 003         | 数学       | 94     |
| 003         | 英语       | 88     |
+-------------+----------+--------+

期望结果

+-------------+--------+---------+---------+
| student_id  | yuwen  | shuxue  | yingyu  |
+-------------+--------+---------+---------+
| 001         | 89     | 95      | 77      |
| 002         | 92     | 83      | 97      |
| 003         | 81     | 94      | 88      |
+-------------+--------+---------+---------+

行转列-相关知识

• a
• b

行转列-SQL实现

我们之前使用case when+sum的方式，现在使用pivot的方式进行转换。

执行SQL

select *
from t_student_score
pivot(
    sum(score) as score
    for subject in('语文' as yuwen,'数学' as shuxue,'英语' as yingyu)
)

执行结果

+-------------+--------+---------+---------+
| student_id  | yuwen  | shuxue  | yingyu  |
+-------------+--------+---------+---------+
| 003         | 81     | 94      | 88      |
| 001         | 89     | 95      | 77      |
| 002         | 92     | 83      | 97      |
+-------------+--------+---------+---------+

行转列-数据准备

--建表语句
CREATE TABLE IF NOT EXISTS t_student_score
(
    student_id string, -- 学生id
    subject    string, -- 学科
    score      bigint  -- 分数
)
    COMMENT '学生成绩表';

insert into t_student_score
values ('001', '语文', 89),
       ('001', '数学', 95),
       ('001', '英语', 77),
       ('002', '语文', 92),
       ('002', '数学', 83),
       ('002', '英语', 97),
       ('003', '语文', 81),
       ('003', '数学', 94),
       ('003', '英语', 88);

二、多列转多行（横表转竖表）

原始数据为一张横表，分别有三列成绩列，想要转成竖表，需要转换成三列分别为 学生id、学科、成绩，转换完成之后学生id将不再是主键。

列转行-基础数据

有学生成绩表，包含学生id、语文、数学、英语三科成绩

+-------------+--------+---------+---------+
| student_id  | yuwen  | shuxue  | yingyu  |
+-------------+--------+---------+---------+
| 001         | 89     | 95      | 77      |
| 002         | 92     | 83      | 97      |
| 003         | 81     | 94      | 88      |
+-------------+--------+---------+---------+

期望结果

+-------------+----------+--------+
| student_id  | subject  | score  |
+-------------+----------+--------+
| 001         | 语文       | 89     |
| 001         | 数学       | 95     |
| 001         | 英语       | 77     |
| 002         | 语文       | 92     |
| 002         | 数学       | 83     |
| 002         | 英语       | 97     |
| 003         | 语文       | 81     |
| 003         | 数学       | 94     |
| 003         | 英语       | 88     |
+-------------+----------+--------+

列转行-相关知识

• a
• b

列转行-SQL实现

我们之前使用case when+sum的方式，现在使用pivot的方式进行转换。

执行SQL

SELECT * FROM t_student_score_02
    UNPIVOT INCLUDE NULLS (
        score FOR subject IN (yuwen AS `语文`, shuxue AS `数学`,  yingyu AS `英语`)
    )

执行结果

+-------------+----------+--------+
| student_id  | subject  | score  |
+-------------+----------+--------+
| 001         | 语文       | 89     |
| 001         | 数学       | 95     |
| 001         | 英语       | 77     |
| 002         | 语文       | 92     |
| 002         | 数学       | 83     |
| 002         | 英语       | 97     |
| 003         | 语文       | 81     |
| 003         | 数学       | 94     |
| 003         | 英语       | 88     |
+-------------+----------+--------+

注意

1. 因为subject中的别名要作为subject的内容，我们需要使用汉字，同时在语法上是别名，所以需要时`` ，不是用''。
2. 注意score 和 subject的顺序。

列转行-数据准备

--建表语句
CREATE TABLE IF NOT EXISTS t_student_score_02
(
    student_id string, -- 学生id
    yuwen      bigint,--语文成绩
    shuxue     bigint, --数学成绩
    yingyu     bigint  --英语成绩
)
    COMMENT '学生成绩表';
--数据插入语句
insert into t_student_score_02
select student_id,
       sum(case when subject = '语文' then score end) as yuwen,
       sum(case when subject = '数学' then score end) as shuxue,
       sum(case when subject = '英语' then score end) as yingyu
from t_student_score
group by student_id

常见坑点

坑1：横表转竖表的选型

横表转竖表有三种主流方案：stack 函数、UNION ALL、UNPIVOT 子句。stack 最灵活但语法复杂，UNION ALL 最简单但多次扫表性能差，UNPIVOT 最简洁但 Spark 3.4+ 才支持。

坑2：竖表转横表的性能陷阱

当分类值很多时（如100个地区），CASE WHEN 方案会产生100个列，SQL 非常冗长。此时应优先用 PIVOT 或程序端透视图。

坑3：数据类型一致性

多列转多行时，stack 要求所有列的数据类型一致。如果列类型不同（如字符串和数字混合），需要先 CAST 为统一类型。

面试怎么考

• "横表转竖表有哪些方法？" → stack / UNION ALL / UNPIVOT
• "PIVOT 和 CASE WHEN 有什么区别？" → 语法糖 vs 手动透视图
• "行列转换在大数据量下的性能怎么优化？" → 减少全表扫描次数，用 PIVOT/UNPIVOT 内置函数

📱关注公众号

「数据仓库技术」文章同步更新，不错过每一篇干货

💬加群交流

备注「数据仓库技术」加入社群，每日一道大厂SQL真题