1.基础用法:LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX),就像聚合函数一样,通过Group by语句,把每个Group的一个字段,拼接起来
LISTAGG(...DUAL UNION ALL
SELECT 'JAPAN' NATION ,'TOKYO' CITY FROM DUAL
)
SELECT
NATION,LISTAGG(CITY,',') WITHIN...查询部门为20的员工列表:SELECT t.DEPTNO,t.ENAME FROM SCOTT.EMP t where t.DEPTNO = '20';
运行结果:
使用 listagg() WITHIN...GROUP () 将多行合并成一行:
SELECT
T .DEPTNO,
LISTAGG (T .ENAME, ',') WITHIN GROUP (ORDER BY T .ENAME) NAMES...JAPAN' NATION ,'TOKYO' CITY FROM DUAL
)
SELECT POPULATION,
NATION,
CITY,
LISTAGG(CITY,',') WITHIN