Split comma separated values into multiple rows - Oracle SQL

Question

I have a table

table1

TEST_TYPE                   H_LEVEL                     HADOOP
22RETGGEDGDD, RRMMNFNEDGDD  Control Directory 1         Interchange Control Header
22RETGGEDGDD, RRMMNFNEDGDD  Control Directory 2         Interchange Control Header
22RETGGEDGDD, RRMMNFNEDGDD  Control Directory 3         Interchange Control Header

I know how to separate comma separated values in columns, but I don't know how to separate values in rows while copying all the other columns as follows.

table2

TEST_TYPE           H_LEVEL                     HADOOP
22RETGGEDGDD        Control Directory 1         Interchange Control Header
RRMMNFNEDGDD        Control Directory 1         Interchange Control Header
22RETGGEDGDD        Control Directory 2         Interchange Control Header
RRMMNFNEDGDD        Control Directory 2         Interchange Control Header
22RETGGEDGDD        Control Directory 3         Interchange Control Header
RRMMNFNEDGDD        Control Directory 3         Interchange Control Header

How can I do this in Oracle SQL?

Possible duplicate of [oracle -- Split multiple comma separated values in oracle table to multiple rows](https://stackoverflow.com/questions/18770581/oracle-split-multiple-comma-separated-values-in-oracle-table-to-multiple-rows) — mustaccio, Apr 22 '19 at 19:42

score 3 · Accepted Answer · answered Apr 22 '19 at 19:51

Here's one option; you need lines 6 onwards.

SQL> with table1 (test_type, h_level, hadoop) as
  2    (select '22RETGGEDGDD, RRMMNFNEDGDD', 'Control Directory 1', 'Interchange Control Header' from dual
  3     union all
  4     select '22RETGGEDGDD, RRMMNFNEDGDD', 'Control Directory 2', 'Interchange Control Header' from dual
  5    )
  6  select trim(regexp_substr(test_type, '[^,]+', 1, column_value)) test_type,
  7         h_level,
  8         hadoop
  9  from table1 join table(cast(multiset(select level from dual
 10                                       connect by level <= regexp_count(test_type, ',') + 1
 11                                      ) as sys.odcinumberlist )) on 1 = 1
 12  order by 2, 1 desc, 3;

TEST_TYPE            H_LEVEL             HADOOP
-------------------- ------------------- --------------------------
22RETGGEDGDD         Control Directory 1 Interchange Control Header
RRMMNFNEDGDD         Control Directory 1 Interchange Control Header
22RETGGEDGDD         Control Directory 2 Interchange Control Header
RRMMNFNEDGDD         Control Directory 2 Interchange Control Header

SQL>

Barbaros Özhan · Answer 2 · 2019-04-22T20:08:41.710

You can use cross join

with t(test_type, h_level, hadoop) as
(
 select '22RETGGEDGDD, RRMMNFNEDGDD','Control Directory 1','Interchange Control Header' 
   from dual union all
 select '22RETGGEDGDD, RRMMNFNEDGDD','Control Directory 2','Interchange Control Header' 
   from dual union all
 select '22RETGGEDGDD, RRMMNFNEDGDD','Control Directory 3','Interchange Control Header' 
   from dual  
)    
select distinct regexp_substr(test_type,'[^, ]+', 1, level ) as test_type, 
                t2.h_level, t2.hadoop
  from t t1
 cross join ( select h_level, hadoop from t ) t2
 connect by level <= regexp_count(test_type,',' ) + 1
   order by t2.h_level, test_type desc;

Demo

Split comma separated values into multiple rows - Oracle SQL

2 Answers2