Tuesday, 20 May 2008

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.

12 comments:

Anonymous said...

Thanks a bunch, chief. You saved me a LOOOOOOOT of time.

Anonymous said...

Thank you very much!!

Anonymous said...

or use xmlagg:

1 select deptno, rtrim( xmlagg(xmlelement(c, ename || ',') order by ename).extract('//text()'), ',') as employees
2 from emp
3* group by deptno
/

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

Hittu said...

XMLAgg is awesome

angad said...

nice post.

and 'mr anonymous', XMLAgg is pretty cool too! thanks...

Anonymous said...

Hello

Great share, thanks for your time

Anonymous said...

Hi there

Just wanted to show my appreciation for your time and hard work

Abdeali said...

XMLAgg is Awesome ! saved my day !

Anonymous said...

I usually dont leave comments but i must say your example to convert rows into comma seperated single row made my day.

Anonymous said...

XMLAGG is awesome example. It helped alot !!!!

John Clinton Woods said...

Thank you so much for the step by step instructions on how to do this! I had seen other examples that got me part of the way there. This explanation rocks!!!

Music said...

Thanks a lot !!!