By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test ( word0 VARCHAR(255),
word1 VARCHAR(255),
word2 VARCHAR(255),
word3 VARCHAR(255)
);
INSERT INTO test VALUES
('word 1','word 2','word 3','word 4'),
('word 2','word 2','word 4','word 5'),
('word 3','word 4','word 2','word 2'),
('word 4','word 5','word 1','word 3');
SELECT * FROM test;
| word0 | word1 | word2 | word3 |
|---|---|---|---|
| word 1 | word 2 | word 3 | word 4 |
| word 2 | word 2 | word 4 | word 5 |
| word 3 | word 4 | word 2 | word 2 |
| word 4 | word 5 | word 1 | word 3 |
WITH
cte1 AS (
SELECT *, ROW_NUMBER() OVER () identity
FROM test
),
cte2 AS (
SELECT word0 word, identity FROM cte1 UNION ALL
SELECT word1 word, identity FROM cte1 UNION ALL
SELECT word2 word, identity FROM cte1 UNION ALL
SELECT word3 word, identity FROM cte1
)
SELECT LEAST(t1.word, t2.word), GREATEST(t1.word, t2.word), COUNT(DISTINCT identity)
FROM cte2 t1
JOIN cte2 t2 USING( identity )
WHERE t1.word > t2.word
GROUP BY 1, 2;
| LEAST(t1.word, t2.word) | GREATEST(t1.word, t2.word) | COUNT(DISTINCT identity) |
|---|---|---|
| word 1 | word 2 | 1 |
| word 1 | word 3 | 2 |
| word 1 | word 4 | 2 |
| word 1 | word 5 | 1 |
| word 2 | word 3 | 2 |
| word 2 | word 4 | 3 |
| word 2 | word 5 | 1 |
| word 3 | word 4 | 3 |
| word 3 | word 5 | 1 |
| word 4 | word 5 | 2 |