Saturday, November 24, 2007

How to print comma separated reslul set

latest360@test>
SELECT deptno
, LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
KEEP (DENSE_RANK LAST ORDER BY curr),',') AS concatenated
FROM ( SELECT deptno
, ename
, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr
, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev
FROM emp )
GROUP BY deptno
CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno
START WITH curr = 1;


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

No comments: