How to pivot a column to a CSV list in Oracle

Suppose we have a column of data that we wish to display as a CSV list. One solution is to develop a pipelined table function in PL/SQL, but how can we achieve the same results using just SQL? This article shows how the Oracle SYS_CONNECT_BY_PATH function can be used to solve the problem.

Example

Using the employee table from the Oracle demo schema, we can easily display a list of employee names for each department.

SELECT deptno, ename
FROM emp
ORDER BY deptno;

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

14 rows selected

But suppose we wish to format the names as a comma separated list as follows.

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

3 rows selected

SYS_CONNECT_BY_PATH

The technique described here uses the SYS_CONNECT_BY_PATH function to provide the CSV column. Let's remind ourselves of what SYS_CONNECT_BY_PATH actually does by displaying a a hierarchy of employees.

SELECT 
  empno,
  mgr,
  level,
  SYS_CONNECT_BY_PATH(ename, '/') path 
  FROM emp
 START WITH ename ='KING'
       CONNECT BY PRIOR empno = mgr;

EMPNO   MGR        LEVEL     PATH
------- ---------- --------- --------------------------
7839               1         /KING
7566    7839       2         /KING/JONES
7788    7566       3         /KING/JONES/SCOTT
7876    7788       4         /KING/JONES/SCOTT/ADAMS
7902    7566       3         /KING/JONES/FORD
7369    7902       4         /KING/JONES/FORD/SMITH
7698    7839       2         /KING/BLAKE
7499    7698       3         /KING/BLAKE/ALLEN
7521    7698       3         /KING/BLAKE/WARD
7654    7698       3         /KING/BLAKE/MARTIN
7844    7698       3         /KING/BLAKE/TURNER
7900    7698       3         /KING/BLAKE/JAMES
7782    7839       2         /KING/CLARK
7934    7782       3         /KING/CLARK/MILLER

14 rows selected

SYS_CONNECT_BY_PATH returns the path of a column value from root to node, with column values separated by a '/' (or whichever character we specify) for each row returned by CONNECT BY condition.

Now this function is only valid in hierarchical queries and unfortunately, in our example, there is no natural hierarchy in our data that we can use. Therefore, we must manufacture one using analytic functions.

Analytic Functions

Let's add a couple more columns to our original query.

SELECT deptno,
       ename,
       row_number() over(PARTITION BY deptno ORDER BY deptno) rnum,
       count(*) over(PARTITION BY deptno) tot
FROM emp;

DEPTNO       ENAME      RNUM  TOT                  
------------ ---------- ------ ----- 
10           CLARK      1      3                      
10           KING       2      3                      
10           MILLER     3      3                      
20           SMITH      1      5                      
20           ADAMS      2      5                      
20           FORD       3      5                      
20           SCOTT      4      5                      
20           JONES      5      5                      
30           ALLEN      1      6                      
30           BLAKE      2      6                      
30           MARTIN     3      6                      
30           JAMES      4      6                      
30           TURNER     5      6                      
30           WARD       6      6

14 rows selected

The RNUM column creates an artificial hierarchy within department which allows us to identify the parent using rnum – 1. TOT tells us the total number of employees for the current department. We use this to identify the employees who are at the bottom of the hierarchy (where rnum=tot).

Complete Solution

So, putting all this together, we get the following SQL.

SELECT 
  deptno,
  LTRIM(SYS_CONNECT_BY_PATH(ename, ','), ',') employees
  FROM (
    SELECT deptno,
           ename,
           row_number() over(PARTITION BY deptno ORDER BY deptno) rnum,
           count(*) over(PARTITION BY deptno) tot
    FROM emp
  )
WHERE rnum=tot
START WITH rnum = 1  
CONNECT BY PRIOR rnum = rnum -1 AND PRIOR deptno = deptno;

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

3 rows selected

We create an arbitrary hierarchy of employees within department and select only those employees at the bottom of the hierarchy. Then we use SYS_CONNECT_BY_PATH with a comma delimiter to format the employee names as a CSV list. LTRIM removes the first comma from our list.

Clearly, this is not the most intuitive SQL statement ever written. However, we could use this statement to create a view which would simplify access to the data. Alternatively, we could write a bespoke pipelined table function to provide a programmatic solution.