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 listagg().

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

Added information based on further conversation with MT0.

I edited this "Added information" again based on even more conversation with MT0. Thank you MT0 for keeping me on my toes!

In the solution below I do away with the magic number 100 and instead I use a common technique to deal with multiple input rows and connect by level from dual. I also show a common technique for dealing with duplicates (in the tokens obtained from the comma-separated input strings).

Query:

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
         ),
     prep ( b_id, i_id, n, token ) as (
       select b_id, i_id, level,
                regexp_substr(r_id, '([^,]+)', 1, level, null, 1)
       from   test_data t
       connect by level <= regexp_count(r_id, ',') + 1
              and prior r_id = r_id             -- to only generate the rows needed
              and prior sys_guid() is not null  -- this is unique, to avoid cycles
     ),
     z ( b_id, i_id, n, token, rn ) as (
       select b_id, i_id, n, token,
              row_number() over (partition by i_id, token order by n)
       from prep
     )
select z.b_id, z.i_id, 
       listagg(t.b_id, '|') within group (order by z.n) as r_id
from   z join test_data t
              on z.token = t.i_id
where  z.rn = 1
group by z.b_id, z.i_id
order by i_id;

Result:

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

5 rows selected.

Decided to add another answer since it uses an entirely different method - the recursive subquery factoring, available since Oracle version 11.2.

I did some testing, with an input (persistent) table called test_data with 9000 rows, each r_id being a comma-separated string of 200 tokens; structure very similar to the OP's small sample in the original post. I tried three methods: the hierarchical query (using connect by with the prior sys_guid() trick), which I proposed; the solution based on a correlated subquery and nested table, posted by MT0; and the recursive query I will show below. In each case, I used the query as the select... portion of a CTAS statement.

• The hierarchical query took 39 minutes to complete.
• The correlated subquery and nested table method took 18 minutes.
• The recursive subquery factoring method took 13 minutes.

(To compare apples to apples I modified MT0's query, removing the extra information that the "tokens" in r_id are numbers - I treated them as strings, as do the other two methods.)

Recursive query:

with
     prep ( b_id, i_id, str, n, st_pos, end_pos, token) as (
       select  b_id, i_id, ',' || r_id || ',', -1, null, 1, null
         from  test_data
       union all
       select  b_id, i_id, str, n+1, end_pos + 1, instr(str, ',', 1, n+3),
               substr(str, st_pos, end_pos - st_pos)
         from  prep
         where end_pos != 0
     ),
     z ( b_id, i_id, n, token, rn ) as (
       select b_id, i_id, n, token,
              row_number() over (partition by i_id, token order by n)
       from prep
     )
select z.b_id, z.i_id, 
       listagg(t.b_id, '|') within group (order by z.n) as r_id
from   z join test_data t
              on z.token = t.i_id
where  z.rn = 1
group by z.b_id, z.i_id
;

Actually one can squeeze a little bit of extra performance; in the anchor part of the recursive CTE (the first member of the union all in the definition of prep), I could start from n = 0, st_pos = 1 and end_pos = the position of the first comma (actually the second in comma in the altered string; I find it a lot easier to add commas at the beginning and the end of the input CSV string and write the recursive CTE as I did.) However, this saves just one iteration out of 200 for each string; so a 0.5% of execution time could be saved. I find the way I wrote the recursive CTE easier to follow.

For completeness, here is the modified version of the "nested table" method I used (credit @MT0):

select b_id,
       i_id,
       ( select listagg(t.b_id, '|') within group (order by rownum)
         from   table ( 
                        cast ( 
                               multiset (
                                  select distinct regexp_substr(d.r_id, '[^,]+', 1, level) 
                                  from dual
                                  connect by level <= regexp_count(d.r_id, ',') + 1
                               ) 
                               as sys.odcivarchar2list
                        ) 
                ) v
                inner join test_data t
                on (v.column_value = t.i_id)
       )
from   test_data d;