SQL> select * from test id name ---- ----- 1 a 2 b 1 c 2 d SQL> select id,wm_concat(name) from test group by id id name ---- ------ 1 a,c 2 b,d
Oracle 12C之后该函数已经不再支持了
LISTAGG
LISTAGG是Oracle 11g中推出的函数,也可以用来做行转列
SQL> select distinct DEPTNO,listagg(ENAME,';') WITHIN GROUP (order by ename) over (PARTITION BY DEPTNO) from SCOTT.EMP; DEPTNO LISTAGG(ENAME,';')WITHINGROUP(ORDERBYENAME) ------- --------------------------------------------- 10 CLARK;KING;MILLER 20 ADAMS;FORD;JONES;SCOTT;SMITH 30 ALLEN;BLAKE;JAMES;MARTIN;TURNER;WARD
SQL> select distinct DEPTNO,listagg(ENAME,';') WITHIN GROUP (order by ename) from SCOTT.EMP group by DEPTNO; DEPTNO LISTAGG(ENAME,';')WITHINGROUP(ORDERBYENAME) ------- --------------------------------------------- 10 CLARK;KING;MILLER 20 ADAMS;FORD;JONES;SCOTT;SMITH 30 ALLEN;BLAKE;JAMES;MARTIN;TURNER;WARD
SQL> select DEPTNO,XMLAGG(XMLELEMENT(CONTENT,ename||',') ORDER BY ename).EXTRACT('//text()').getclobval() as ename from scott.emp group by DEPTNO; DEPTNO ENAME ------- ------------------------------------- 10 CLARK,KING,MILLER, 20 ADAMS,FORD,JONES,SCOTT,SMITH, 30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD,
SQL> select DEPTNO,xmlagg(xmlparse(content ENAME ||',' wellformed) order by ENAME).getclobval() as ename from SCOTT.EMP GROUP BY DEPTNO; DEPTNO ENAME ------- ------------------------------------- 10 CLARK,KING,MILLER, 20 ADAMS,FORD,JONES,SCOTT,SMITH, 30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD,