Columns to Rows in Oracle SQL

slavong — Tue, 14 Mar 2023 15:36:31 +0000

Based on the amount of posts in the web and questions from my colleagues, this seems to be a quite common challenge. That's why I decided to write this post with the goal of going beyond what you typically find in stackoverflow answers and comments.

So what is the problem? We have a column that contains some list and we want to turn it into rows. So from this ...

col
a,b,c

we want to get this ...

result
a
b
c

I already hear you saying: "But this is not a good design!". True, first normal form (1.NF) does not allow the source table to look like above. But the reality check says, that not all developers (and data modellers) know 1.NF and even less follow it, often for good reasons, e.g. to build some kind of staging area in their database.

Let us start with the test data. We will use Oracle dual "table" for this purpose

SELECT 'a,b,c' AS col 
FROM dual

and to improve readability of the SQL statements below, we'll pack it into a WITH clause

WITH test AS (
SELECT 'a,b,c' AS col 
FROM dual )
SELECT * FROM test

The most common solution is to use REGEXP_SUBSTR and REGEXP_COUNT functions to achieve the expected result

WITH test AS (
SELECT 'a,b,c' AS col 
FROM dual )
SELECT LEVEL AS n, REGEXP_SUBSTR(col, '[^,]+', 1, LEVEL) AS out
FROM test
CONNECT BY LEVEL <= REGEXP_COUNT(col, ',') + 1

We get

N	OUT
1	a
2	b
3	c

But this "solution" has one big drawback. Let's try to put more than just one row into test data

WITH test AS (
SELECT 'a,b,c' AS col
FROM dual 
UNION ALL
SELECT 'd,e,f' AS col 
FROM dual )
SELECT LEVEL AS n, REGEXP_SUBSTR(col, '[^,]+', 1, LEVEL) AS out 
FROM test
CONNECT BY LEVEL <= REGEXP_COUNT(col, ',') + 1
ORDER BY n, out

NOTE: ORDER BY was also added as last line to the query above to improve readability of the output.

The output is not what is expected

N	OUT
1	a
1	d
2	b
2	b
2	e
2	e
3	c
3	c
3	c
3	c
3	f
3	f
3	f
3	f

What's going on here? Data in second level is duplicated and on third level it even quadrupled. What's wrong? In order to find out, we add function call for SYS_CONNECT_BY_PATH

WITH test AS (
SELECT 'a,b,c' AS col
FROM dual 
UNION ALL
SELECT 'd,e,f' AS col 
FROM dual )
SELECT LEVEL AS n, REGEXP_SUBSTR(col, '[^,]+', 1, LEVEL) AS out,
SYS_CONNECT_BY_PATH(LEVEL || '+' || REGEXP_SUBSTR(col, '[^,]+', 1, LEVEL), '/') as path
FROM test
CONNECT BY LEVEL <= REGEXP_COUNT(col, ',') + 1
ORDER BY n, out

N	OUT	PATH
1	a	/1+a
1	d	/1+d
2	b	/1+a/2+b
2	b	/1+d/2+b
2	e	/1+d/2+e
2	e	/1+a/2+e
3	c	/1+d/2+b/3+c
3	c	/1+a/2+b/3+c
3	c	/1+d/2+e/3+c
3	c	/1+a/2+e/3+c
3	f	/1+d/2+b/3+f
3	f	/1+d/2+e/3+f
3	f	/1+a/2+b/3+f
3	f	/1+a/2+e/3+f

Output shows, that the query is traversing the path and is jumping from one test data row to the other. I tried using some features like NOCYCLE or adding a unique column to the test data to fix this, but without success. If you have a solution, please let me know.

But there is a solution, in my opinion even more elegant. Welcome to CROSS APPLY join clause.

WITH test AS (
SELECT 'a,b,c' AS col
FROM dual 
UNION ALL
SELECT 'd,e,f' AS col 
FROM dual )
SELECT *
FROM test t
CROSS APPLY 
(SELECT LEVEL AS n, REGEXP_SUBSTR(t.col, '[^,]+', 1, LEVEL) AS out
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT(t.col, ',') + 1)
ORDER BY n, out

The output is exactly as expected

COL	N	OUT
a,b,c	1	a
d,e,f	1	d
a,b,c	2	b
d,e,f	2	e
a,b,c	3	c
d,e,f	3	f

I have a follow up to this article in my mind on how to enhance the query for source data containing strings (or even CLOBs), that cannot be easily parsed by regular expressions. So stay tuned!

That's it, folks! at least for now.

DEV Community: slavong

Columns to Rows in Oracle SQL