Multiple comma separated lists to columns

Question

I'm trying to split a comma separated list in a column to different rows. For example:

with testdata(vala,valb,valc) as(
select
    '1,2,3,4' as vala,
    '5,6,7,8' as valb,
    '9,10,11' as valc
from dual)

Using the table above I'm trying to get the values in such a form that vala,valb and valc are the columns, the different comma separated values are the rows. As shown in the image below:

I already had a look into https://lalitkumarb.wordpress.com/2014/12/02/split-comma-delimited-string-into-rows-in-oracle/ and https://blogs.oracle.com/aramamoo/how-to-split-comma-separated-string-and-pass-to-in-clause-of-select-statement but I seem to be unable to apply the methods described as I have more columns...

The reason why I need to do this transformation is to use these values in a IN expression further down the query.

Possible duplicate of [Splitting string into multiple rows in Oracle](https://stackoverflow.com/questions/14328621/splitting-string-into-multiple-rows-in-oracle) — Ankit Das, Aug 29 '19 at 07:28

score 2 · Accepted Answer · answered Aug 29 '19 at 07:44

This should work, regardless of the column "height".

with testdata(vala, valb, valc) as(
   select '1,2,3,4' as vala
         ,'5,6,7,8' as valb
         ,'9,10,11' as valc
     from dual
)
select regexp_substr(vala,'[^,]+', 1, level) as vala -- Pick the 
      ,regexp_substr(valb,'[^,]+', 1, level) as valb -- value at this 
      ,regexp_substr(valc,'[^,]+', 1, level) as valc -- position or null
  from testdata
  connect by regexp_substr(vala,'[^,]+', 1, level) is not null -- Generate one row if 
          or regexp_substr(valb,'[^,]+', 1, level) is not null -- any of the positions
          or regexp_substr(valc,'[^,]+', 1, level) is not null -- would have a value

@ErsinGülbahar It is not the same answer; yours uses multiple joins and is less efficient whereas this one does not require any joins. — MT0, Aug 29 '19 at 08:29

CompEng · Answer 2 · 2019-08-29T07:44:04.987

1

try this:

with testdata(vala,valb,valc)
 as(
select
    '1,2,3,4' as vala,
    '5,6,7,8' as valb,
    '9,10,11' as valc
from dual)
,
main as (
select rownum rn, 
regexp_substr(vala,'[^,]+', 1, level) data from
testdata
connect by  regexp_substr(vala,'[^,]+', 1, level) is not null
),
sub as (
select  rownum rn, 
regexp_substr(valb,'[^,]+', 1, level)data from
testdata
connect by  regexp_substr(valb,'[^,]+', 1, level) is not null
),
sub2 as (
select  rownum rn, 
regexp_substr(valc,'[^,]+', 1, level)data from
testdata
connect by  regexp_substr(valc,'[^,]+', 1, level) is not null
)
select 
main.data,
sub.data,
sub2.data
 from
main 
full outer join
sub on main.rn=sub.rn
full outer join
sub2 on main.rn=sub2.rn

edited Aug 29 '19 at 07:44

answered Aug 29 '19 at 07:14

CompEng

7,161
16
68
122

This creates the desired output. The issue in this solution is that I need to know in advance which column will have the most elements (main). Is there a way to avoid this. As you are joining on the rownr I would be able to do a count and add a dummy column (with the necessary number of rows) but would like to avoid this. – mtndoe Aug 29 '19 at 07:23
@M.Doe , you can sign it as correct if it is useful for you – CompEng Aug 29 '19 at 11:23

score 0 · Answer 3 · answered Aug 29 '19 at 08:44

A solution that only uses simple string functions (and not slow regular expressions), doesn't need any joins and can also handle multiple input rows:

Oracle Setup:

CREATE TABLE testdata (vala, valb, valc) AS
  SELECT '1,2,3,4','5,6,7,8', '9,10,11' FROM DUAL UNION ALL
  SELECT '12', '13,14,15,16', '' FROM DUAL;

Query:

WITH data ( vala, valb, valc, starta, startb, startc, enda, endb, endc, rn, idx ) AS (
  SELECT vala,
         valb,
         valc,
         1,
         1,
         1,
         INSTR( vala, ',', 1 ),
         INSTR( valb, ',', 1 ),
         INSTR( valc, ',', 1 ),
         ROWNUM,
         1
  FROM   testdata
UNION ALL
  SELECT vala,
         valb,
         valc,
         CASE WHEN enda = 0 THEN 0 ELSE enda + 1 END,
         CASE WHEN endb = 0 THEN 0 ELSE endb + 1 END,
         CASE WHEN endc = 0 THEN 0 ELSE endc + 1 END,
         CASE WHEN enda = 0 THEN 0 ELSE INSTR( vala, ',', enda + 1 ) END,
         CASE WHEN endb = 0 THEN 0 ELSE INSTR( valb, ',', endb + 1 ) END,
         CASE WHEN endc = 0 THEN 0 ELSE INSTR( valc, ',', endc + 1 ) END,
         rn,
         idx + 1
  FROM   data
  WHERE  enda > 0
  OR     endb > 0
  OR     endc > 0
)
SELECT CASE
       WHEN starta = 0 THEN NULL
       WHEN enda   = 0 THEN SUBSTR( vala, starta )
                       ELSE SUBSTR( vala, starta, enda - starta )
       END AS vala,
       CASE
       WHEN startb = 0 THEN NULL
       WHEN endb   = 0 THEN SUBSTR( valb, startb )
                       ELSE SUBSTR( valb, startb, endb - startb )
       END AS valb,
       CASE
       WHEN startc = 0 THEN NULL
       WHEN endc   = 0 THEN SUBSTR( valc, startc )
                       ELSE SUBSTR( valc, startc, endc - startc )
       END AS valc
FROM   data
ORDER BY rn, idx;

Output:

VALA | VALB | VALC
:--- | :--- | :---
1    | 5    | 9   
2    | 6    | 10  
3    | 7    | 11  
4    | 8    | null
12   | 13   | null
null | 14   | null
null | 15   | null
null | 16   | null

db<>fiddle here

Multiple comma separated lists to columns

3 Answers3