Oracle SQL - how to Limit the comma splitting to rows

Question

I have a data like this,

ID      RPT_NAME CSV_STRING
------------------------------
abc123  Test1   AA,BB,AC,AD,EF,GG,FG,FD,DF,GG,AA,PL,KI,LO,TT,TE,LF,FG
abc456  Test2   GF,DS,SA,RE,EW,QQ,QW
def123  Test3   HH

I wish to limit the comma splitting to rows for fixed number of commas. Post that, I would take the next set of records, until all the records have been pushed in rows in groups of 6.

For eg, my offset is 5 commas, I am trying to get the output like this,

ID      RPT_NAME CSV_STRING
----------------------
abc123  Test1   AA,BB,AC,AD,EF,GG
abc123  Test1   GG,FG,FD,DF,GG,AA
abc123  Test1   PL,KI,LO,TT,TE,LF
abc123  Test1   FG
abc456  Test2   GF,DS,SA,RE,EW,QQ
abc456  Test2   QW
def123  Test3   HH

I tried my best to explain it. :)

Can I please get a solution on this? TIA Appreciated

Answer 1

with tab(ID,RPT_NAME,CSV_STRING) as
(
 select 'abc123','Test1','AA,BB,AC,AD,EF,GG,FG,FD,DF,GG,AA,PL,KI,LO,TT,TE,LF,FG' from DUAL
  union all
 select 'abc456','Test2','GF,DS,SA,RE,EW,QQ,QW' from DUAL
  union all
 select 'def123','Test3','HH' from DUAL
)


 select id,rpt_name,
        trim(trailing ',' from regexp_substr(csv_string,'([^,]+(,|$)){1,6}',1,level))
   from tab
connect by regexp_substr(csv_string,'([^,]+(,|$)){1,6}',1,level) is not null
    and prior id=id and prior dbms_random.value is not null

regexp_substr with regular expression ([^,]+(,|$)){1,6} get one to six elements. connect by reproduce row until the string is completed.