Oracle多行合并成一行

在很多情况下需要将多行合并为一行，遇到问题，寻找一下解决方案 1、10g下提供了wmsys.wm_concat来解决 SQL> conn scott/tiger Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 Connected as scott SQL> SQL> select t1.deptno, wmsys.wm_concat(t2.empno) categ_ids 2 from dept t1, emp t2 3 where t1.deptno = t2.deptno 4 group by t1.deptno; DEPTNO CATEG_IDS ------ -------------------------------------------------------------------------------- 10 7782,7839,7934 20 7369,7902,7566 30 7499,7900,7698,7654,7844,7521 SQL> 2、使用sys_connect_by_path函数，sys_connect_by_path + start with ... connect by ... prior + 分析函数 用ltrim实现： SQL> SELECT deptno, ltrim(max(sys_connect_by_path(empno, ',')), ',') categ_ids 2 FROM (SELECT deptno, empno, rn rchild,(rn - 1) rfather 3 FROM (SELECT t1.deptno, 4 t2.empno, 5 row_number() over(PARTITION BY t1.deptno ORDER BY t2.empno) rn 6 FROM dept t1, emp t2 7 where t1.deptno = t2.deptno)) 8 START WITH rfather LIKE '0' 9 C