By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Table1 (id, name) AS
SELECT 1, 'John;Sue' FROM DUAL
1 rows affected
CREATE TABLE Table2 (id, name) AS
SELECT 1, 'Mary;John' FROM DUAL
1 rows affected
WITH t1_positions (id, name, spos, epos) AS (
SELECT id,
name,
1,
INSTR(name, ';', 1)
FROM table1
UNION ALL
SELECT id,
name,
epos + 1,
INSTR(name, ';', epos + 1)
FROM t1_positions
WHERE epos > 0
),
t1_strings (id, item) AS (
SELECT id,
CASE epos
WHEN 0
THEN SUBSTR(name, spos)
ELSE SUBSTR(name, spos, epos - spos)
END
FROM t1_positions
),
t2_positions (id, name, spos, epos) AS (
SELECT id,
name,
1,
INSTR(name, ';', 1)
FROM table2
UNION ALL
SELECT id,
name,
epos + 1,
INSTR(name, ';', epos + 1)
FROM t2_positions
WHERE epos > 0
| ID | NAME |
|---|---|
| 1 | John;Mary;Sue |
DROP TABLE table1;
DROP TABLE table2;
CREATE TABLE table1 (id, name) AS
SELECT 1, 'John' FROM DUAL UNION ALL
SELECT 1, 'Sue' FROM DUAL;
2 rows affected
CREATE TABLE table2 (id, name) AS
SELECT 1, 'Mary' FROM DUAL UNION ALL
SELECT 1, 'John' FROM DUAL;
2 rows affected
SELECT id,
LISTAGG(name, ';') WITHIN GROUP (ORDER BY name) AS name
FROM (SELECT * FROM table1
UNION
SELECT * FROM table2)
GROUP BY id;
| ID | NAME |
|---|---|
| 1 | John;Mary;Sue |