By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 8.0.26 |
WITH data0 (a, b, c, d, n) AS (
SELECT 1, 1, 0, 0, 50 UNION
SELECT 0, 0, 1, 0, 100 UNION
SELECT 0, 1, 1, 1, 200
)
, data1 (a, b, c, d, n, id) AS (
SELECT t.*, ROW_NUMBER() OVER (ORDER BY n) FROM data0 AS t
)
, data2 (event, n, id) AS (
SELECT 'A', n, id FROM data1 WHERE a = 1 UNION
SELECT 'B', n, id FROM data1 WHERE b = 1 UNION
SELECT 'C', n, id FROM data1 WHERE c = 1 UNION
SELECT 'D', n, id FROM data1 WHERE d = 1
)
, data (event, n, id, cnt) AS (
SELECT t.*, COUNT(*) OVER (PARTITION BY id) FROM data2 AS t
)
, tcross (event1, event2, n, id) AS (
SELECT t1.event, COALESCE(t2.event, t1.event), t1.n, t1.id
FROM data AS t1
LEFT JOIN data AS t2
ON t1.id = t2.id
AND t1.event < t2.event
WHERE t2.event IS NOT NULL OR t1.cnt = 1
)
SELECT event1, event2, n
FROM tcross
ORDER BY id, event1, event2
;
| event1 | event2 | n |
|---|---|---|
| A | B | 50 |
| C | C | 100 |
| B | C | 200 |
| B | D | 200 |
| C | D | 200 |