Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Organization (id, name, service) AS
SELECT 1, 'CEO',       '1, 2, 4' FROM DUAL UNION ALL
SELECT 2, 'ALF',       '1, 3, 5' FROM DUAL UNION ALL
SELECT 3, 'FRGT',      NULL      FROM DUAL UNION ALL
SELECT 4, 'OFFICE_IT', '1, 2, 7' FROM DUAL;
4 rows affected

CREATE TABLE City (id, name) AS
SELECT 10, 'Berlin' FROM DUAL UNION ALL
SELECT 20, 'Paris'  FROM DUAL UNION ALL
SELECT 30, 'London' FROM DUAL UNION ALL
SELECT 40, 'Rome'   FROM DUAL;
4 rows affected

CREATE TABLE Relation (oid, cid) AS
SELECT 1, 10 FROM DUAL UNION ALL
SELECT 2, 20 FROM DUAL UNION ALL
SELECT 3, 30 FROM DUAL UNION ALL
SELECT 4, 40 FROM DUAL;
4 rows affected

SELECT o.name as Organization, o.service as service,  c.name as City 
FROM   Organization o
       INNER JOIN Relation rel ON o.id = rel.oid
       INNER JOIN City c ON rel.cid  = c.id 
WHERE  o.service IS NOT NULL 


ORGANIZATION
SERVICE
CITY


CEO
1, 2, 4
Berlin

ALF
1, 3, 5
Paris

OFFICE_IT
1, 2, 7
Rome


SELECT O.ID
     , O.name
     , trim(COLUMN_VALUE) AS service
     , c.name AS city
FROM   Organization o
       CROSS APPLY xmltable(('"' || REPLACE(o.service, ',', '","') || '"'))
       INNER JOIN Relation rel ON o.id = rel.oid
       INNER JOIN City c ON rel.cid  = c.id 
WHERE  o.service IS NOT NULL 


ID
NAME
SERVICE
CITY


1
CEO
1
Berlin

1
CEO
2
Berlin

1
CEO
4
Berlin

2
ALF
1
Paris

2
ALF
3
Paris

2
ALF
5
Paris

4
OFFICE_IT
1
Rome

4
OFFICE_IT
2
Rome

4
OFFICE_IT
7
Rome


SELECT O.ID
     , O.name
     , trim(COLUMN_VALUE) AS service
     , c.name AS city
FROM   Organization o
       CROSS JOIN xmltable(('"' || REPLACE(o.service, ',', '","') || '"'))
       INNER JOIN Relation rel ON o.id = rel.oid
       INNER JOIN City c ON rel.cid  = c.id 
WHERE  o.service IS NOT NULL 


ID
NAME
SERVICE
CITY


1
CEO
1
Berlin

1
CEO
2
Berlin

1
CEO
4
Berlin

2
ALF
1
Paris

2
ALF
3
Paris

2
ALF
5
Paris

4
OFFICE_IT
1
Rome

4
OFFICE_IT
2
Rome

4
OFFICE_IT
7
Rome


SELECT O.ID
     , O.name
     , trim(COLUMN_VALUE) AS service
     , c.name AS city
FROM   Organization o
       INNER JOIN Relation rel ON o.id = rel.oid
       INNER JOIN City c ON rel.cid  = c.id,
       xmltable(('"' || REPLACE(o.service, ',', '","') || '"'))
WHERE  o.service IS NOT NULL 


ID
NAME
SERVICE
CITY


1
CEO
1
Berlin

1
CEO
2
Berlin

1
CEO
4
Berlin

2
ALF
1
Paris

2
ALF
3
Paris

2
ALF
5
Paris

4
OFFICE_IT
1
Rome

4
OFFICE_IT
2
Rome

4
OFFICE_IT
7
Rome


SELECT O.ID
     , O.name
     , trim(COLUMN_VALUE) AS service
     , c.name AS city
FROM   Organization o,
       xmltable(('"' || REPLACE(o.service, ',', '","') || '"')),
       Relation rel, 
       City c
WHERE  o.service IS NOT NULL 
AND    o.id = rel.oid
AND    rel.cid  = c.id


ID
NAME
SERVICE
CITY


1
CEO
1
Berlin

1
CEO
2
Berlin

1
CEO
4
Berlin

2
ALF
1
Paris

2
ALF
3
Paris

2
ALF
5
Paris

4
OFFICE_IT
1
Rome

4
OFFICE_IT
2
Rome

4
OFFICE_IT
7
Rome
ID	NAME	SERVICE	CITY
1	CEO	1	Berlin
1	CEO	2	Berlin
1	CEO	4	Berlin
2	ALF	1	Paris
2	ALF	3	Paris
2	ALF	5	Paris
4	OFFICE_IT	1	Rome
4	OFFICE_IT	2	Rome
4	OFFICE_IT	7	Rome