I'd start by redesigning my schema to not include a comma delimited list of attributes in my tables. I'd be using a relational schema design. Then this will be easy.
I'm serious - who does this?
sigh, it is so sad that since this mistake is made so often, I happen to be able to work with it with my eyes closed. I'm not saying this will be efficient - but it will work.
we have to turn your silly delimited list into proper rows
so we can join (you know, what we do in relational databases)
so I can aggregate the string again to give you back your bad idea....
ops$tkyte%ORA11GR2> with data
2 as
3 (
4 select ci_name, trim(';' from sg ) sg, to_number(substr(column_value,1,3),'000') rn, substr(column_value,4) sg_fixed,
5 column1, column2
6 from (
7 select mytable.*, column_value
8 from (select ci_name, ';'||old_support_groups||';' sg, column1, column2 from mytable) mytable,
9 table( cast( multiset(
10 select
11 to_char(level,'fm000')||
12 trim( substr (sg,
13 instr (sg, ';', 1, level ) + 1,
14 instr (sg, ';', 1, level+1) - instr (sg, ';', 1, level) -1 ) )
15 as token
16 from dual
17 connect by level <= length(sg)-length(replace(sg,';',''))-1
18 ) as sys.odciVarchar2List ) )
19 )
20 ),
21 data2
22 as
23 (
24 select *
25 from data t1, mapping_table t2
26 where t1.sg_fixed = t2.old_support_group
27 )
28 select ci_name, sg, listagg( new_support_group, ';' ) within group (order by rn) new_sg, max(column1), max(column2)
29 from data2
30 group by ci_name, sg
31 order by ci_name
32 /
CI_NAME SG NEW_SG MAX(COLUMN MAX(COLUMN
---------- ------------------------------ -------------------------------------------------- ---------- ----------
Server A SG-B;SG-A;SG-C SupportGroup B;SupportGroup A;SupportGroup C abc xyz
Server B SG-C;SG-X;SG-1;SG-P SupportGroup C;SupportGroup X;SupportGroup one;Sup pqr lmn
portGroup P