By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH all_keys AS (
SELECT 1 AS COLI_KEY UNION ALL
SELECT 2 UNION ALL
SELECT 3
),
data AS (
SELECT 1 AS COLI_DAY, NULL AS COLI_WEE, NULL AS COLI_MON,
NULL AS COLI_YEA UNION ALL
SELECT NULL, 1, NULL, NULL UNION ALL
SELECT NULL, NULL, 1, NULL UNION ALL
SELECT NULL, NULL, NULL, 1
),
yourTable AS (
SELECT 1 AS COLI_KEY, 1 AS COLI_DAY, NULL AS COLI_WEE, 1 AS COLI_MON, NULL AS COLI_YEA UNION ALL
SELECT 2, NULL, NULL, 1, 1 UNION ALL
SELECT 3, 1, 1, 1, 1
)
SELECT
a.COLI_KEY,
d.COLI_DAY,
d.COLI_WEE,
d.COLI_MON,
d.COLI_YEA
FROM all_keys a
CROSS JOIN data d
INNER JOIN yourTable t
ON t.COLI_KEY = a.COLI_KEY AND
(t.COLI_DAY = d.COLI_DAY OR
t.COLI_WEE = d.COLI_WEE OR
t.COLI_MON = d.COLI_MON OR
t.COLI_YEA = d.COLI_YEA);
| COLI_KEY | COLI_DAY | COLI_WEE | COLI_MON | COLI_YEA |
|---|---|---|---|---|
| 1 | 1 | null | null | null |
| 1 | null | null | 1 | null |
| 2 | null | null | 1 | null |
| 2 | null | null | null | 1 |
| 3 | 1 | null | null | null |
| 3 | null | 1 | null | null |
| 3 | null | null | 1 | null |
| 3 | null | null | null | 1 |