By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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
8 rows affected
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
2 rows affected
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
| FAMILY_NAME | BROTHERS | SISTERS |
|---|---|---|
| Stark | Robb,Bran,Rickon | Sansa,Arya |
| Lannister | Jaime,Tyrion | Cersei |
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
| FAMILY_NAME | BROTHERS | SISTERS |
|---|---|---|
| Stark | Robb,Bran,Rickon | Sansa,Arya |
| Lannister | Jaime,Tyrion | Cersei |