select username, id, wmsys.wm_concat(subject) as subject, wmsys.wm_concat(score) as score
from STUDENTSCORES
groupby username, id
方案二:listagg函数
select username, id, LISTAGG(subject, '-') within group(orderby subject) as subject, LISTAGG(score, ',') within group(orderby score) as score
from STUDENTSCORES
groupby username, id
方案三:常规sql
select username, id, translate(ltrim(subject, '/'), '*/', '*,') as subject,translate(ltrim (score, '/'), '*/', '*,') as score
from
(select row_number() over (partition by username, id orderby username, id, lvl desc) as rn, username, id, subject, score
from
(select username, id, level lvl, sys_connect_by_path (subject, '/') as subject, sys_connect_by_path (score, '/') as score
from
(select username, id, subject, score, row_number() over (partition by username,id orderby username, id) as num from STUDENTSCORES orderby username, id)
connectby username = prior username and id = prior id and num - 1 = prior num))
where rn = 1;