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

​x
 
ref_no  cus_no  amount                              dateAA1      123    325000,200000,317085,2657915        20190522,20190405,20190402,20190322AA2      265    1000000,1500000                     20190125,20190102AA3      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

 
ref_no  cus_no  amount            dateAA1      123    325000            20190522AA1      123    200000            20190405AA1      123    317085            20190402AA1      123    2657915           20190322AA2      265    1000000           20190125AA2      265    1500000           20190102AA3      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

 
SELECT ref_no,cus_no,trim(regexp_substr(amount, '[^,]+',1,LEVEL)),trim(regexp_substr(date, '[^,]+', 1,LEVEL))FROM MY_TABLE_FILECONNECT 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

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

Advertisement

Answer