By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;
| BANNER |
|---|
| Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production |
| PL/SQL Release 11.2.0.2.0 - Production |
| CORE 11.2.0.2.0 Production |
| TNS for Linux: Version 11.2.0.2.0 - Production |
| NLSRTL Version 11.2.0.2.0 - Production |
WITH
table1 AS(
SELECT 4782 ID, 'OIL & GAS' DESK, '' as SEGMENT, '' as SUPERVISOR FROM dual
UNION ALL
SELECT 4782 ID, 'AUTOMOTIVE' DESK, '' as SEGMENT, '' as SUPERVISOR FROM dual),
table2 AS(
SELECT 4782 ID, '' AS DESK, 'GLOBAL' SEGMENT, '' as SUPERVISOR FROM dual),
table3 AS(
SELECT 4782 ID, '' AS DESK, '' as SEGMENT, 'DANIEL' SUPERVISOR FROM dual
UNION ALL
SELECT 4782 ID, '' AS DESK, '' as SEGMENT, 'JAMES' SUPERVISOR FROM dual)
SELECT table1.ID, table1.DESK, table2.SEGMENT, (select SUPERVISOR from (select SUPERVISOR, ROWNUM AS RN FROM table3) WHERE RN = 1) SUPERVISOR
FROM table1 JOIN table2 on table1.ID = table2.ID
WHERE table1.DESK = 'OIL & GAS'
UNION ALL
SELECT table1.ID, table1.DESK, null SEGMENT, (select SUPERVISOR from (select SUPERVISOR, ROWNUM AS RN FROM table3) WHERE RN = 2) SUPERVISOR
FROM table1 JOIN table2 on table1.ID = table2.ID
WHERE table1.DESK = 'AUTOMOTIVE'
| ID | DESK | SEGMENT | SUPERVISOR |
|---|---|---|---|
| 4782 | OIL & GAS | GLOBAL | DANIEL |
| 4782 | AUTOMOTIVE | null | JAMES |