Hello, I have a column in the table that has a current value for example '29|1|3004|3|2|24'
I want to rearrange the value in the order to '|1|2|3|24|29|3004'
I'm trying to do that without creating temp table or any holding place is there way to do it in 1 SQL statement?
This should apply to multiple record table
SQL> with t as
2 ( select 1 pk, '29|1|3004|3|2|24' str from dual
3 union all
4 select 2 pk, '2|17|304|32|21|24' str from dual
5 ),
6 d as
7 ( select '|' delim from dual ),
8 list_as_rows as
9 (
10 select pk, to_number(substr( delim||str||delim ,
11 instr( delim||str||delim , delim ,1,idx)+ length(delim) ,
12 instr( delim||str||delim , delim ,1,idx+1) -
13 instr( delim||str||delim , delim ,1,idx) -length(delim))) item
14 from t,
15 d,
16 lateral( select level idx
17 from dual
18 connect by level <= (length(str) -
19 length(replace(str,delim)))/length(delim) +1)
20 )
21 select pk, listagg(item,'|') within group ( order by item ) as rearranged
22 from list_as_rows
23 group by pk;
PK REARRANGED
---------- ------------------------------
1 1|2|3|24|29|3004
2 2|17|21|24|32|304
2 rows selected.