By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH myTable1(ID,desk) AS
(
SELECT 4782, 'OIL & GAS' FROM dual UNION ALL
SELECT 4782, 'AUTOMOTIVE' FROM dual
), myTable2(ID,segment) AS
(
SELECT 4782, 'GLOBAL' FROM dual
), myTable3(ID,supervisor) AS
(
SELECT 4782, 'DANIEL' FROM dual UNION ALL
SELECT 4782, 'JAMES' FROM dual
)
SELECT NVL(NVL(t2.ID,t3.ID),t1.ID) AS ID, desk, segment, supervisor
FROM ( SELECT t1.*, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY 0) AS rn FROM myTable1 t1 ) t1
FULL JOIN ( SELECT t2.*, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY 0) AS rn FROM myTable2 t2 ) t2
ON t2.ID = t1.ID AND t2.rn = t1.rn
FULL JOIN ( SELECT t3.*, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY 0) AS rn FROM myTable3 t3 ) t3
ON t3.ID = t1.ID AND t3.rn = t1.rn
| ID | DESK | SEGMENT | SUPERVISOR |
|---|---|---|---|
| 4782 | AUTOMOTIVE | GLOBAL | JAMES |
| 4782 | OIL & GAS | null | DANIEL |