Use SYS_CONNECT_BY_PATH in Oracle9i to convert rows and columns

Tables available: SQL> SELECT deptno, ename FROM emp ORDER BY deptno, ename; deptno ename ------ ---------- 10 CLARK 10 KING 10 MILLER 20 ADAMS 20 FORD 20 JONES 20 SCOTT 20 SMITH 30 ALLEN 30 blke 30 JAMES 30 MARTIN 30 TURNER 30 WARD 14 rows selected. output: deptno ename ------ ---------- 10 CLARK, KING, MILLER 20 ADAMS, FORD, JONES, SCOTT, SMITH 30 ALLEN, BLAKE, JAMES, MARTIN, TURNER, in addition to using clustering functions or stored procedures, WARD can: SQL> SELECT deptno 2, LTRIM (MAX (SYS_CONNECT_BY_PATH (ename, ',') in 9i ,',')) 3 KEEP (DENSE_RANK last order by curr), ',') AS concatenated 4 FROM (SELECT deptno 5, ename 6, ROW_NUMBER () OVER (partition by deptno order by ename) AS curr 7, ROW_NUMBER () OVER (partition by deptno order by ename)-1 AS prev 8 FROM emp) 9 group by deptno 10 connect by prev = PRIOR curr AND deptno = PRIOR deptno 11 start with curr = 1; deptno concatenated 10 CLARK, KING, MILLER 20 ADAMS, FORD, JONES, SCOTT, SMITH 30 ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD author thomas01042003