5.列转行-多列转多行（横表变竖表）

+-------------+--------+---------+---------+
| 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     |
+-------------+----------+--------+

--语文成绩
select student_id,
       '语文' as subject,
       yuwen  as score
from t_student_score_02
union all
--数学成绩
select student_id,
       '数学' as subject,
       shuxue as score
from t_student_score_02
union all
--英语成绩
select student_id,
       '英语' as subject,
       yingyu as score
from t_student_score_02

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

select student_id,
       concat_ws(',', concat('语文:', yuwen), concat('数学:', shuxue), concat('英语:', yingyu)) as sub_scores
from t_student_score_02

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

select student_id,
       split(sub_score, ':')[0] as subject,
       split(sub_score, ':')[1] as score
from (select student_id,
             concat_ws(',', concat('语文:', yuwen), concat('数学:', shuxue), concat('英语:', yingyu)) as sub_scores
      from t_student_score_02)
         lateral view explode(split(sub_scores, ',')) t as sub_score

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

--建表语句
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