Comma Separated Values in Table to Rows

Comma Separated String to Multiple Rows, this is a very common and simple problem we usually face. I have also posted some examples of it in my previous posts. With this post I am trying to start with simple example and take it to next level so convert Comma Separated Data Stored in Table to Rows.

Let me first create sample data for examples, I will use SCOTT schema for this.

SQL> create table dept_emp_csv
  2  as
  3  select
  4     d.deptno,
  5     d.dname,
  6     listagg(ename,',') within group (order by ename) ename_csv
  7  from
  8     emp e,
  9     dept d
 10  where
 11     e.deptno = d.deptno
 12  group by
 13     d.deptno,
 14     d.dname;
Table created.

SQL> select * from dept_emp_csv;

    DEPTNO DNAME          ENAME_CSV
---------- -------------- ---------------------------------------
        10 ACCOUNTING     CLARK,KING,MILLER
        20 RESEARCH       ADAMS,FORD,JONES,SCOTT,SMITH
        30 SALES          ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

Now we have a table "dept_emp_csv" which has a column storing list of employee names in a comma separated format. We want to convert this comma saperated data in multiple rows, to acheieve output as by following query.

SQL>   SELECT d.deptno, d.dname, ename
  2      FROM emp e, dept d
  3     WHERE e.deptno = d.deptno
  4  ORDER BY d.deptno, d.dname, e.ename;

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

Let's first convert single comma separated string to rows "CLARK,KING,MILLER".

SQL> WITH T AS (SELECT 'CLARK,KING,MILLER' TXT FROM DUAL)
  2      SELECT REGEXP_SUBSTR (TXT,'[^,]+',1,LEVEL) ENAME
  3        FROM T
  4  CONNECT BY REGEXP_SUBSTR (TXT,'[^,]+',1,LEVEL) IS NOT NULL;

ENAME
-----------------
CLARK
KING
MILLER

Oh, it was simple. But our actual requirement is little complex. Lets use above logic to convert table containing Comma Separated Column into Multiple Rows using Hierarchical Query (connect by).

SQL>   SELECT deptno,
  2           dname,
  3           REGEXP_SUBSTR (ename_csv, '[^,]+',1,n) AS ename
  4      FROM dept_emp_csv,
  5           (    SELECT LEVEL n
  6                  FROM DUAL,
  7                       (SELECT MAX (REGEXP_COUNT (ename_csv, ',')) + 1 mcomma FROM dept_emp_csv)
  8            CONNECT BY LEVEL <= mcomma) ctr
  9     WHERE ctr.n <= 1 + REGEXP_COUNT (ename_csv, ',')
 10  ORDER BY deptno, dname, ename;

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

Above worked perfectly and converted comma separated values to rows. We can also achieve the same using Recursive Sub-query Factoring as following

SQL> WITH csv (deptno, dname, ename_csv, ename, step)
  2       AS (SELECT deptno, dname,
  3                  REGEXP_SUBSTR (ename_csv, '[^,]+'),
  4                  SUBSTR (ename_csv || ',', INSTR (ename_csv || ',', ',') + 1),
  5                  1
  6             FROM dept_emp_csv
  7           UNION ALL
  8           SELECT deptno, dname,
  9                  REGEXP_SUBSTR (ename, '[^,]+'),
 10                  SUBSTR (ename || ',', INSTR (ename || ',', ',') + 1),
 11                  step + 1
 12             FROM csv
 13            WHERE ename_csv IS NOT NULL)
 14    SELECT deptno, dname, ename_csv ename
 15      FROM csv
 16     WHERE ename_csv is not null
 17  ORDER BY deptno, dname, ename;

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

Converting Comma Separated Values to rows using "Recursive Sub-query Factoring" got quite complicated. We can also achieve the same using XMLTABLE by following simple query.

SQL> SELECT deptno, dname,
  2         (COLUMN_VALUE).getstringval () ename
  3    FROM (SELECT deptno,
  4                 dname,
  5                 '"' || REPLACE (ename_csv, ',', '","') || '"' ename_csv
  6            FROM dept_emp_csv) dept_emp_csv,
  7         XMLTABLE (ename_csv)
  8   ORDER BY deptno, dname, ename;

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

I hope that you have enjoyed this post . Comments are well appreciated.

Related Posts:
- LISTAGG - ON OVERFLOW TRUNCATE - Oracle 12c R2
- New String Aggregation Techniques
- Compare Values of Two Comma Separated Strings in Oracle
- Comma Separated to Rows