This article shows simple method to duplicate records based on value in a column.

For example following query returns 3 records

select 'a' letter, 2 num from dual
union all
select 'b', 4 from dual
union all
select 'c', 4 from dual

LETTER        NUM
------ ----------
a               2 
b               4 
c               4

to duplicate records based on column NUM you can do following transformation using CONNECT BY

with a as (
select 'a' letter, 2 num from dual
union all
select 'b', 4 from dual
union all
select 'c', 4 from dual
)
select a.*, b.column_value 
from a,
     table(cast(multiset(
                 select level from dual
                 connect by level <= num ) 
           as sys.odciNumberList)) b;

LETTER        NUM COLUMN_VALUE
------ ---------- ------------
a               2            1 
a               2            2 
b               4            1 
b               4            2 
b               4            3 
b               4            4 
c               4            1 
c               4            2 
c               4            3 
c               4            4

You can use above method CONNECT BY to transform complex string (comma separated string) in a column into multiple records and simple string(split based on comma)

select 'a,b,c' letter from dual
union all
select 'g,h' from dual
union all
select 'm,k,l' from dual

LETTER
------
a,b,c  
g,h    
m,k,l

and now simple

with a as (                         
select 'a,b,c' letter from dual
union all
select 'g,h' from dual
union all
select 'm,k,l' from dual
)
select a.*, b.column_value 
  from a,
     table(cast(multiset(
                  select regexp_substr(letter,'[^,]+',1,level) 
                    from dual
                  connect by level <= regexp_count(letter, ',')+1)
           as sys.odciVarchar2List)) b;

LETTER COLUMN_VALUE    
------ ----------------
a,b,c  a               
a,b,c  b               
a,b,c  c               
g,h    g               
g,h    h               
m,k,l  m               
m,k,l  k               
m,k,l  l

Have a fun 🙂
Tomasz