如果实现 部分行标签 转置为列名

如下为数据原表score

其中当课程course_id 为1时，代表课程为语文chinese, courese_id=2时 为数学math； course_id=3时，课程为english

student_id course_id score_id avg_student_score
8
7 3 98 93.5
7 2 89 93.5
6 3 34 32.5
6 1 31 32.5
5 2 87 81.5
5 1 76 81.5
4 3 20 33.3
4 2 30 33.3
4 1 50 33.3
3 3 80 80
3 2 80 80
3 1 80 80
2 3 80 70
2 2 60 70
2 1 70 70
1 3 99 89.7
1 2 90 89.7
1 1 80 89.7
需要转置为 目标为下列表格 score2

student_id chinese math english avg_student_score
7 null 89 98 93.5
1 80 90 99 89.67
5 76 87 null 81.5
3 80 80 80 80
2 70 60 80 70
4 50 30 20 33.33
6 31 null 34 32.5

我尝试了

with t1 as
(select s.s_id,
(case when c_id =01 then tmp.s_score end ) as chinese,
(case when c_id =02 then tmp.s_score end ) as math,
(case when c_id =03 then tmp.s_score end ) as english,
avg_score
from score s where
s.chinese is not null and s.math is not null and s.english is not null;

没成功 ，有哪路朋友有办法吗

最简单的方式,你最后这个sql稍微改下

select s.s_id,
sum(case when c_id ='01' then tmp.s_score  end ) as chinese,
sum(case when c_id ='02' then tmp.s_score  end ) as math,
sum(case when c_id ='03' then tmp.s_score  end ) as english,
max(avg_score) as avg_score
from score s
group by s.s_id;