When working with hierarchical or grouped data in Oracle SQL, a common requirement is to aggregate values and then split them back into individual columns. The query below demonstrates how to do this using LISTAGG and REGEXP_SUBSTR:

SELECT deptno,
       REGEXP_SUBSTR(val, '[^|]+', 1, 1) AS part1,
       REGEXP_SUBSTR(val, '[^|]+', 1, 2) AS part2,
       REGEXP_SUBSTR(val, '[^|]+', 1, 3) AS part3,
       REGEXP_SUBSTR(val, '[^|]+', 1, 4) AS part4,
       REGEXP_SUBSTR(val, '[^|]+', 1, 5) AS part5,
       REGEXP_SUBSTR(val, '[^|]+', 1, 6) AS part6
FROM (
  SELECT deptno,
         LISTAGG(ename, '|') WITHIN GROUP (ORDER BY deptno) AS val
  FROM emp
  GROUP BY deptno
);

Here’s what happens step by step:

  • LISTAGG concatenates all employee names for each department, using a pipe | as the delimiter.
  • REGEXP_SUBSTR extracts each portion of the string based on the delimiter, producing individual columns (PART1, PART2, etc.).

Alternative Approaches

1. Using CONNECT BY with REGEXP_SUBSTR
Instead of splitting into fixed columns, you can unpivot the data into multiple rows:

SELECT deptno,
       REGEXP_SUBSTR(val, '[^|]+', 1, level) AS part
FROM (
  SELECT deptno,
         LISTAGG(ename, '|') WITHIN GROUP (ORDER BY deptno) AS val
  FROM emp
  GROUP BY deptno
)
CONNECT BY REGEXP_SUBSTR(val, '[^|]+', 1, level) IS NOT NULL
AND PRIOR deptno = deptno
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL;

This approach dynamically splits the values into rows rather than limiting to six columns.

2. Using XMLTABLE for more flexibility
If your Oracle version supports it, XMLTABLE is a cleaner way to split strings:

SELECT deptno, x.column_value AS ename
FROM (
  SELECT deptno,
         REPLACE(LISTAGG(ename, '|') WITHIN GROUP (ORDER BY deptno), '|', '') AS xml_val
  FROM emp
  GROUP BY deptno
) t,
XMLTABLE(('<root><ename>' || t.xml_val || '</ename></root>')/ename) x;

This avoids hardcoding the number of parts and allows Oracle to parse the list as XML elements.