oracle SQL — Replace comma separated string with another set of values
<p>I have the following table:</p>

<pre><code>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
</code></pre>

<p>I need output like below using sql</p>

<pre><code>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
</code></pre>

<ol>
<li>R_ID should match its value with I_ID and pick the corresponding B_ID.</li>
<li>Commas present in R_ID column should replaced with '|' delimiter.</li>
</ol>

<p>Example: 1st row R_ID has values 1235,1237. 1235 and 1237 is present in I_ID so their corresponding B_ID is picked i.e B00001,X00001 and expected output is B00001|X00001</p>