oracle将列名进行拼接,[Oracle]将一列值按照另一列分组拼接

同事碰到的问题，怎么把一列值按照另一列分组拼成字符串。

比如下面两列：

SQL> select deptno,ename from emp where deptno is not null;

20 SMITH

30 ALLEN

30 WARD

20 JONES

30 MARTIN

30 BLAKE

10 CLARK

20 SCOTT

10 KING

30 TURNER

20 ADAMS

30 JAMES

20 JAMES

10 MILLER

14 rows selected.

将ename根据deptno的值拼成字符串。

实现方法一： 用分析函数row_number() over进行分组然后用sys_connect_by_path进行拼接：

select max(sys_connect_by_path(ename, ',')) name,deptno

from (select ename, deptno, row_number() over(partition by deptno order by ename) ro

from (select deptno,ename from emp where deptno is not null)

) newtab

start with newtab.ro = 1

connect by prior newtab.ro = newtab.ro - 1

group by deptno;

结果为：

,CLARK,KING,MILLER,SCOTT,TURNER,WARD                       30

,CLARK,KING,MILLER,SCOTT,SMITH                             20

,CLARK,KING,MILLER                                         10

实现方法二： 更简便，用函数wm_concat(列名)，该函数可以把列值以逗号分隔，并显示成一行。

SQL> select DEPTNO,wm_concat(ENAME) n from emp where deptno is not null group by DEPTNO;

DEPTNO N ---------- --------------------------------------------------         10 CLARK,MILLER,KING         20 SMITH,JAMES,ADAMS,SCOTT,JONES         30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD