Table Rows to One String in SQL

I came across of very cool feature yesterday that I had not seen before. It was the ability to take values of a column in multiple rows and concatenate them into one string. This is useful for building a TO distribution list for an email. Here is an example of concatenating all the names of the employees in the EMP table:

SQL> SELECT *
2    FROM emp;

EMPNO ENAME      JOB             MGR  HIREDATE  SAL        COMM       DEPTNO
----- ---------- --------------- ---- --------- ---------- ---------- ----------
7839 KING        PRESIDENT            17-NOV-81       5000                    10
7698 BLAKE       MANAGER         7839 01-MAY-81       2850                    30
7782 CLARK       MANAGER         7839 09-JUN-81       2450                    10
7566 JONES       MANAGER         7839 02-APR-81       2975                    20
7788 SCOTT       ANALYST         7566 09-DEC-82       3000                    20
7902 FORD        ANALYST         7566 03-DEC-81       3000                    20
7369 SMITH       CLERK           7902 17-DEC-80        800                    20
7499 ALLEN       SALESMAN        7698 20-FEB-81       1600        300         30
7521 WARD        SALESMAN        7698 22-FEB-81       1250        500         30
7654 MARTIN      SALESMAN        7698 28-SEP-81       1250       1400         30
7844 TURNER      SALESMAN        7698 08-SEP-81       1500          0         30
7876 ADAMS       CLERK           7788 12-JAN-83       1100                    20
7900 JAMES       CLERK           7698 03-DEC-81        950                    30
7934 MILLER      CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL>
SQL> SELECT LTRIM(SYS_CONNECT_BY_PATH(myvalues, ','),',') catvalues
2    FROM (SELECT myvalues, ROW_NUMBER() OVER (ORDER BY myvalues) rn, COUNT(*) OVER () cnt
3            FROM (SELECT ename myvalues
4                    FROM emp
5                 )
6         ) data
7   WHERE rn = cnt
8   START WITH rn = 1
9   CONNECT BY PRIOR rn = rn-1;

CATVALUES
-----------------------------------------------------------------------------------
ADAMS,ALLEN,BLAKE,CLARK,FORD,JAMES,JONES,KING,MARTIN,MILLER,SCOTT,SMITH,TURNER,WARD

Here is the SELECT statement:

SELECT LTRIM(SYS_CONNECT_BY_PATH(myvalues, ','),',') catvalues
FROM (SELECT myvalues, ROW_NUMBER() OVER (ORDER BY myvalues) rn, COUNT(*) OVER () cnt
FROM (SELECT ename myvalues
      FROM emp
   )
) data
WHERE rn = cnt
START WITH rn = 1
CONNECT BY PRIOR rn = rn-1;

To figure this out let us look at the inner SELECT:

SQL>
SQL> SELECT myvalues, ROW_NUMBER() OVER (ORDER BY myvalues) rn, COUNT(*) OVER () cnt
2    FROM (SELECT ename myvalues
3            FROM emp
4         );

MYVALUES           RN        CNT
---------- ---------- ----------
ADAMS               1         14
ALLEN               2         14
BLAKE               3         14
CLARK               4         14
FORD                5         14
JAMES               6         14
JONES               7         14
KING                8         14
MARTIN              9         14
MILLER             10         14
SCOTT              11         14
SMITH              12         14
TURNER             13         14
WARD               14         14

14 rows selected.

Oracle starts at the top where

rn = 1

which is ADAMS. It connects each node of the tree to the prior element where the prior's RN value is one less

rn = rn -1

until WARD. Now the SYS_CONNECT_BY_PATH function in a CONNECT BY query takes the tree and concatenates the values together putting a comma ',' between the nodes in the tree.