Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE lookup (id, descr) AS
SELECT 1, 'maths'     FROM DUAL UNION ALL
SELECT 2, 'reading'   FROM DUAL UNION ALL
SELECT 3, 'science'   FROM DUAL UNION ALL
SELECT 4, 'history'   FROM DUAL UNION ALL
SELECT 5, 'chemistry' FROM DUAL UNION ALL
SELECT 6, 'physics'   FROM DUAL;
6 rows affected

CREATE TABLE cases (id, name, subjects) AS
SELECT 1, 'ABC', 'maths,reading'     FROM DUAL UNION ALL
SELECT 2, 'DEF', 'maths,chemistry'  FROM DUAL UNION ALL
SELECT 3, 'GHI', 'physics,chemistry' FROM DUAL;
3 rows affected

CREATE TABLE case_subjects (caseid NUMBER, subjectid NUMBER);
SELECT c.id AS caseid,
       l.id AS subjectid
FROM   cases c
       INNER JOIN lookup l
       ON (','|| c.subjects || ',' LIKE '%,' || l.descr || ',%')


CASEID
SUBJECTID


1
1

1
2

2
1

2
5

3
5

3
6


INSERT INTO case_subjects(caseid, subjectid)
SELECT c.id,
       l.id
FROM   cases c
       INNER JOIN lookup l
       ON (','|| c.subjects || ',' LIKE '%,' || l.descr || ',%')
6 rows affected

SELECT * FROM case_subjects;


CASEID
SUBJECTID


1
1

1
2

2
1

2
5

3
5

3
6