在ORACLE中聚集合并字符串

sql中有聚集函数sum，但只能计算数值型，如（本文中试验都是在scott用户下）：

SQL> select deptno，sum(sal) asal

from emp a

group by deptno;

DEPTNO ASAL

30 9400

20 10875

10 8750

有时要想分组查看每个部门的人员列表如下结果：

DEPTNO ENAME

-

10 CLARK，KING，MILLER

20 SMITH，FORD，ADAMS，SCOTT，JONES

30 ALLEN，BLAKE，MARTIN，TURNER，JAMES，WARD

标准sql没有提供这个方法。

SQL> select deptno，sum(ename) ename

from emp a

group by deptno;

select deptno，sum(ename) ename

*

第 1 行出现错误:

ORA-01722: 无效数字

oracle10g提供了一个函数wmsys.wm_concat可以实现：

SQL> select deptno，wmsys.wm_concat(ename) ename

from emp a

group by deptno;

DEPTNO ENAME

-

10 CLARK，KING，MILLER

20 SMITH，FORD，ADAMS，SCOTT，JONES

30 ALLEN，BLAKE，MARTIN，TURNER，JAMES，WARD

正是想要的结果。

但是在10g以下版本怎么办呢？

在oracle9i可以用connect by 实现，但成本很高：

SQL> select deptno，substr(max(sys_connect_by_path(ename，‘，‘))，2) ename

from (select a.*，row_number()over(partition by deptno order by empno) rn from emp a )

group by deptno

start with rn=1

connect by rn-1=prior rn and deptno=prior deptno

order by deptno;

DEPTNO ENAME

-

10 CLARK，KING，MILLER

20 SMITH，JONES，SCOTT，ADAMS，FORD

30 ALLEN，WARD，MARTIN，BLAKE，TURNER，JAMES

结果也正确。这个sql参考了一下其他人的例子，并用到了分析函数。