Mixed use of Oracle column row conversion and comma truncated string

oracle The mixed use of column to row conversion and string truncation with commas

lately , When writing a stored procedure to return a value to the front end , The mixed use of row to column and intercepted string is used

Data to be converted

Data you want to achieve

Realization SQL

```sql
select OLD_REEL,OLD_QTY,substr(REEL,0,decode(instr(REEL, ','),0,length(REEL),instr(REEL, ',') - 1)) as newreel1,
substr(qty1,0,decode(instr(qty1, ','),0,length(qty1),instr(qty1, ',') - 1)) as reelqty1,
substr(REEL,decode(instr(REEL, ','),0,length(REEL),instr(REEL, ',') + 1)) as newreel2,
substr(qty1,decode(instr(qty1, ','),0,length(qty1),instr(qty1, ',') + 1)) as reelqty2 from (select OLD_REEL,  
OLD_QTY,  
listagg(NEW_REEL,',') within GROUP (order by NEW_REEL) over (partition by OLD_REEL) REEL,  
listagg(qty,',') within GROUP (order by qty) over (partition by OLD_QTY) qty1  
from WMS_REEL_IQC where OLD_REEL='551R17083000056') t where rownum=1;

#####  explain ：1.
`select OLD_REEL,  
OLD_QTY,  
listagg(NEW_REEL,',') within GROUP (order by NEW_REEL) over (partition by OLD_REEL) REEL,  
listagg(qty,',') within GROUP (order by qty) over (partition by OLD_QTY) qty1  
from WMS_REEL_IQC where OLD_REEL='551R17083000056'`  this SQL Realize row to column conversion , The function used listagg

2.`select OLD_REEL,OLD_QTY,substr(REEL,0,decode(instr(REEL, ','),0,length(REEL),instr(REEL, ',') - 1)) as newreel1,
substr(qty1,0,decode(instr(qty1, ','),0,length(qty1),instr(qty1, ',') - 1)) as reelqty1,
substr(REEL,decode(instr(REEL, ','),0,length(REEL),instr(REEL, ',') + 1)) as newreel2,
substr(qty1,decode(instr(qty1, ','),0,length(qty1),instr(qty1, ',') + 1)) as reelqty2` this SQL Implementation with comma ‘,’ Intercepting string , If you want to intercept other strings , Replace the comma with the character you want .