1.wm_concat

2.pivot

3.sum(decode/case when)   group by

4.listagg

select

  stu_code

 ,listagg(score, ',')   within group(order by score)

 ,listagg(subject,',')  within group(order by subject)

from stu_subject_score

group by stu_code

;