db2分组后上下合并不一样的列

1、
SELECT stno,LISTAGG(subname||' '||score,',') WITHIN GROUP(ORDER BY 1) info
FROM (VALUES ('NO01','语文',90) , ('NO01','数学',99) , ('NO01','英语',98) , ('NO02','语文',96),('NO02','数学',95)) AS t(stno,subname,score)
GROUP BY stno;


2、
SELECT stno,REPLACE(REPLACE(XML2CLOB(XMLAGG(XMLELEMENT(NAME stno,subname||' '||score||' , '))),'<STNO>'),'</STNO>') info
FROM (VALUES ('NO01','语文',90) , ('NO01','数学',99) , ('NO01','英语',98) , ('NO02','语文',96),('NO02','数学',95)) AS t(stno,subname,score)
GROUP BY stno;