1. 字符串少于4000时候,使用 LISTAGG
SELECT WORKDEPT,
LISTAGG(LASTNAME, ', ') WITHIN GROUP(ORDER BY LASTNAME)
AS EMPLOYEES
FROM EMP
GROUP BY WORKDEPT;
2. 字符串大于4000 时候
SELECT dept,
replace(replace(XMLAGG ( XMLELEMENT ( NAME a, e.lname) ORDER BY e.lname),'</A>',''),'<A>','')
FROM employees e
GROUP BY dept;