行列转换-横表竖表互相转换
一、多行转多列（竖表转横表）
原始数据中是一个竖表，每个学生的每个学科一行数据，对其转换成一张横表，即表中学生id为主键，包含语文、数学、英语三列，列值为对应学科分数。
1、基础数据
有学生成绩表，包含学生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 |
+-------------+--------+---------+---------+
2.相关知识
3.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 |
+-------------+--------+---------+---------+
4、数据准备
--建表语句
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将不再是主键。
1、基础数据
有学生成绩表，包含学生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 |
+-------------+----------+--------+
2.相关知识
3.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 |
+-------------+----------+--------+
注意
- 因为subject中的别名要作为subject的内容，我们需要使用汉字，同时在语法上是别名，所以需要时`` ，不是用''。
- 注意score 和 subject的顺序。
4、数据准备
--建表语句
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
本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2024-09-12，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
腾讯云开发者
