How to split column into rows Oracle sql?

I have a table which I need two columns split into rows

eg: Table name : MY_TABLE_FILE

ref_no  cus_no  amount                              date
AA1      123    325000,200000,317085,2657915        20190522,20190405,20190402,20190322
AA2      265    1000000,1500000                     20190125,20190102
AA3      457    475000                              20190101

Output expected

ref_no  cus_no  amount            date
AA1      123    325000            20190522
AA1      123    200000            20190405
AA1      123    317085            20190402
AA1      123    2657915           20190322
AA2      265    1000000           20190125
AA2      265    1500000           20190102
AA3      457    475000            20190101

Code which i tried

SELECT ref_no,cus_no,
trim(regexp_substr(amount, '[^,]+',1,LEVEL)),
trim(regexp_substr(date, '[^,]+', 1,LEVEL))
FROM MY_TABLE_FILE
CONNECT BY LEVEL <= regexp_count(amount, ',')+1

but i’m not get the output, Appreciate if you could help with the code

Answer

FIDDLE DEMO

SELECT distinct ref_no, cus_no, 
       trim(regexp_substr(amount, '[^,]+', 1, level)) amount, 
       trim(regexp_substr(dater, '[^,]+', 1, level)) dater
FROM (SELECT ref_no,cus_no, amount,dater FROM TAB) t
CONNECT BY instr(amount, ',', 1, level - 1) > 0  
ORDER BY ref_no

Advertisement

Answer