SQL> WITH A AS (SELECT 10 DEPTNO,'CLARK' ENAME FROM DUAL
2 UNION
3 SELECT 10 DEPTNO,'KING' ENAME FROM DUAL
4 UNION
5 SELECT 10 DEPTNO,'MILLER' ENAME FROM DUAL
6 UNION
7 SELECT 20 DEPTNO,'ADAMS' ENAME FROM DUAL
8 UNION
9 SELECT 20 DEPTNO,'FORD' ENAME FROM DUAL
10 UNION
11 SELECT 20 DEPTNO,'JONES' ENAME FROM DUAL
12 UNION
13 SELECT 20 DEPTNO,'SCOTT' ENAME FROM DUAL
14 UNION
15 SELECT 20 DEPTNO,'SMITH' ENAME FROM DUAL
16 UNION
17 SELECT 30 DEPTNO,'ALLEN' ENAME FROM DUAL
18 UNION
19 SELECT 30 DEPTNO,'BLAKE' ENAME FROM DUAL
20 UNION
21 SELECT 30 DEPTNO,'JAMES' ENAME FROM DUAL
22 UNION
23 SELECT 30 DEPTNO,'MARTIN' ENAME FROM DUAL
24 UNION
25 SELECT 30 DEPTNO,'WARD' ENAME FROM DUAL
26 )
27 select DEPTNO,substr(MAX(SYS_CONNECT_BY_PATH(ENAME,',')),2) ENAME FROM
28 (SELECT DEPTNO,ENAME,ROW_NUMBER()OVER(PARTITION BY DEPTNO ORDER BY DEPTNO ) RN FROM A)
29 START WITH RN=1
30 CONNECT BY RN-1=PRIOR RN and deptno=prior deptno
31 GROUP BY DEPTNO
32 order by DEPTNO
33 /
DEPTNO ENAME
---------- --------------------------------------------------------------------------------
10 MILLER,KING,CLARK
20 JONES,FORD,ADAMS,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,WARD
