How can I exchange ids with names in comma data in oracle

I have a table like below which has names and ids.

ID	Name
1	Robb
2	Sansa
3	Arya
4	Bran
5	Rickon
6	Cersei
7	Jaime
8	Tyrion

And there is another table like below

Family Name	brothers	sisters
Stark	1,4,5	2,3
Lennister	7,8	6

I need a query which will give brothers' name for stark family in one row with separate by comma

Family Name	brothers	sisters
Stark	Robb,Bran,Rickon	Sansa,Arya

Thank you for help

Solution

You can use correlated sub-queries and check whether the id column is a substring of the brothers or sisters and then use LISTAGG to aggregate the matched names.

Assuming that you want the name to be in the same order as the id are in the brothers or sisters lists then you could use:

SELECT family_name,
       (
          SELECT LISTAGG(b.name, ',')
                   WITHIN GROUP (
                     ORDER BY INSTR(','||f.brothers||',', ','||b.id||',')
                   )
          FROM   people b
          WHERE  INSTR(','||f.brothers||',', ','||b.id||',') > 0
       ) AS brothers,
       (
          SELECT LISTAGG(s.name, ',')
                   WITHIN GROUP (
                     ORDER BY INSTR(','||f.sisters||',', ','||s.id||',')
                   )
          FROM   people s
          WHERE  INSTR(','||f.sisters||',', ','||s.id||',') > 0
       ) AS sisters
FROM   families f

However, if you want the name to be sorted by id value then you could use:

SELECT family_name,
       (
          SELECT LISTAGG(b.name, ',') WITHIN GROUP (ORDER BY b.id)
          FROM   people b
          WHERE  ','||f.brothers||',' LIKE '%,'||b.id||',%'
       ) AS brothers,
       (
          SELECT LISTAGG(s.name, ',') WITHIN GROUP (ORDER BY s.id)
          FROM   people s
          WHERE  ','||f.sisters||',' LIKE '%,'||s.id||',%'
       ) AS sisters
FROM   families f

Which, for your sample data:

CREATE TABLE people (ID, Name) AS
SELECT 1, 'Robb' FROM DUAL UNION ALL
SELECT 2, 'Sansa' FROM DUAL UNION ALL
SELECT 3, 'Arya' FROM DUAL UNION ALL
SELECT 4, 'Bran' FROM DUAL UNION ALL
SELECT 5, 'Rickon' FROM DUAL UNION ALL
SELECT 6, 'Cersei' FROM DUAL UNION ALL
SELECT 7, 'Jaime' FROM DUAL UNION ALL
SELECT 8, 'Tyrion' FROM DUAL;

CREATE TABLE families (Family_Name, brothers, sisters ) AS
SELECT 'Stark', '1,4,5', '2,3' FROM DUAL UNION ALL
SELECT 'Lannister', '7,8', '6' FROM DUAL;

Both output:

FAMILY_NAME BROTHERS SISTERS

Stark Robb,Bran,Rickon Sansa,Arya

Lannister Jaime,Tyrion Cersei

If you just want the Stark row then add a filter condition as the final line to either query:

WHERE  family_name = 'Stark'

db<>fiddle here