create table student(
       id number,
       name varchar2(20),
       course varchar2(20),
       score number
)

select id, name, wm_concat(score) scores from student group by id, name;

select id,name,sum(decode(course,‘Chinese‘,score,null)) "Chinese",
               sum(decode(course,‘Math‘,score,null)) "Math",
               sum(decode(course,‘English‘,score,null)) "English"
       from student
       group by id,name

select id,name,sum(case when course=‘Chinese‘ then score end) "Chinese",
               sum(case when course=‘Math‘ then score end) "Math",
               sum(case when course=‘English‘ then score end) "English"
from student
group by id,name

create table scores as
select id,name,sum(case when course=‘Chinese‘ then score end) "Chinese",
               sum(case when course=‘Math‘ then score end) "Math",
               sum(case when course=‘English‘ then score end) "English"
from student
group by id,name
order by i

select id,name,‘Chinese‘ as course from scores
union
select id,name,‘Math‘ as course from scores
union
select id,name,‘English‘ as course from scores