I have the following table:
B_ID I_ID R_ID
W00001 1234 1235,1237
B00001 1235 1236,1235
T00001 1236 1235,1235,1235
X00001 1237 1234,1236,1238
M00001 1238 1238
B_ID I_ID R_ID
W00001 1234 B00001|X00001
B00001 1235 T00001|B00001
T00001 1236 B00001
X00001 1237 W00001|T00001|M00001
M00001 1238 M00001
1. R_ID should match its value with I_ID and pick the corresponding B_ID.
2. Comma's present in R_ID column should replaced with '|' delimiter.
In the solution below, I use a standard technique to split each comma-separated string into components (tokens) in factored subquery
prep. Then I join back to the original table to replace each token (which is an
i_id) with the corresponding
b_id, and then put the tokens back together into pipe-separated strings with
Note: This solution assumes that each
r_id has fewer than 100 tokens (see the "magic number" 100 in the definition of
idx). If it is known that each
r_id will have no more than 9 tokens, then 100 can be changed to 10 (resulting in faster processing). If NO upper bound is known beforehand, you can change 100 to some ridiculously large number; 4000 will do if
r_id is anything but a CLOB, as VARCHAR2 and such have a limit of 4000 characters.
Thanks to MT0 for reminding me to add this note.
with test_data ( b_id, i_id, r_id ) as ( select 'W00001', 1234, '1235,1237' from dual union all select 'B00001', 1235, '1236,1235' from dual union all select 'T00001', 1236, '1235,1235,1235' from dual union all select 'X00001', 1237, '1234,1236,1238' from dual union all select 'M00001', 1238, '1238' from dual ), idx ( n ) as ( select level from dual connect by level < 100 ), prep ( b_id, i_id, n, token ) as ( select t.b_id, t.i_id, i.n, regexp_substr(t.r_id, '([^,]+)', 1, i.n, null, 1) from test_data t join idx i on i.n <= regexp_count(t.r_id, ',') + 1 ) select p.b_id, p.i_id, listagg(t.b_id, '|') within group (order by p.n) as r_id from prep p join test_data t on p.token = t.i_id group by p.b_id, p.i_id order by p.i_id; B_ID I_ID R_ID ------ ---------- ------------------------------ W00001 1234 B00001|X00001 B00001 1235 T00001|B00001 T00001 1236 B00001|B00001|B00001 X00001 1237 W00001|T00001|M00001 M00001 1238 M00001