By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Data (
Col1 VARCHAR(10),
Col2 VARCHAR(10),
Col3 VARCHAR(MAX)
)
INSERT INTO Data
VALUES
('PSPID_1', 'POSID_10', 'FREI ABGS EROF'),
('PSPID_1', 'POSID_11', 'ABGS EROF'),
('PSPID_1', 'POSID_12', 'FREI ABGS'),
('PSPID_2', 'PSPID_20', 'FREI ABGS'),
('PSPID_2', 'PSPID_21', 'EROF'),
('PSPID_3', 'POSID_30', 'WXYZ EROF'), -- ABGS replaced with WXYZ in this group
('PSPID_3', 'POSID_31', 'WXYZ EROF'),
('PSPID_3', 'POSID_32', 'FREI WXYZ EROF'),
('PSPID_3', 'POSID_33', 'FREI WXYZ'),
('FALSE_1', 'FALSE_11', 'ABCD ABCD'), -- Duplicate value in one row
('FALSE_1', 'FALSE_12', 'EFGH IJKL'),
('FALSE_2', 'FALSE_22', 'ABCD EFGH'), -- Dup Col2 values - False hit
('FALSE_2', 'FALSE_22', 'IJKL'),
('MATCH_3', 'FALSE_33', 'ABCD EFGH'), -- Dup Col2 values - false miss
('MATCH_3', 'FALSE_33', 'ABCD EFGH')
15 rows affected
SELECT
D.Col1, D.Col1GroupCount, S.value, COUNT(*) AS Col3ItemCount,
CASE WHEN COUNT(*) = D.Col1GroupCount THEN 'Match' ELSE '' END AS Match
FROM (
SELECT *, COUNT(*) OVER(PARTITION BY Col1) AS Col1GroupCount
FROM Data D
) D
CROSS APPLY STRING_SPLIT(D.col3, ' ') S
WHERE S.value > ''
GROUP BY D.Col1, D.Col1GroupCount, S.value
--HAVING COUNT(*) = D.Col1GroupCount
| Col1 | Col1GroupCount | value | Col3ItemCount | Match |
|---|---|---|---|---|
| FALSE_1 | 2 | ABCD | 2 | Match |
| FALSE_1 | 2 | EFGH | 1 | |
| FALSE_1 | 2 | IJKL | 1 | |
| FALSE_2 | 2 | ABCD | 1 | |
| FALSE_2 | 2 | EFGH | 1 | |
| FALSE_2 | 2 | IJKL | 1 | |
| MATCH_3 | 2 | ABCD | 2 | Match |
| MATCH_3 | 2 | EFGH | 2 | Match |
| PSPID_1 | 3 | ABGS | 3 | Match |
| PSPID_1 | 3 | EROF | 2 | |
| PSPID_1 | 3 | FREI | 2 | |
| PSPID_2 | 2 | ABGS | 1 | |
| PSPID_2 | 2 | EROF | 1 | |
| PSPID_2 | 2 | FREI | 1 | |
| PSPID_3 | 4 | EROF | 3 | |
| PSPID_3 | 4 | FREI | 2 | |
| PSPID_3 | 4 | WXYZ | 4 | Match |
WITH SelectedCol1Values AS (
SELECT DISTINCT D.Col1
FROM (
SELECT *, COUNT(*) OVER(PARTITION BY Col1) AS Col1GroupCount
FROM Data D
) D
CROSS APPLY STRING_SPLIT(D.col3, ' ') S
WHERE S.value > ''
GROUP BY D.Col1, D.Col1GroupCount, S.value
HAVING COUNT(*) = D.Col1GroupCount
)
SELECT D.Col1, D.Col2
FROM SelectedCol1Values S
JOIN Data D
ON D.Col1 = S.Col1
| Col1 | Col2 |
|---|---|
| PSPID_1 | POSID_10 |
| PSPID_1 | POSID_11 |
| PSPID_1 | POSID_12 |
| PSPID_3 | POSID_30 |
| PSPID_3 | POSID_31 |
| PSPID_3 | POSID_32 |
| PSPID_3 | POSID_33 |
| FALSE_1 | FALSE_11 |
| FALSE_1 | FALSE_12 |
| MATCH_3 | FALSE_33 |
| MATCH_3 | FALSE_33 |
WITH SelectedCol1Values AS (
SELECT DISTINCT D.Col1
FROM (
SELECT *, COUNT(*) OVER(PARTITION BY Col1) AS Col1GroupCount
FROM Data D
) D
CROSS APPLY STRING_SPLIT(D.col3, ' ') S
WHERE S.value > ''
GROUP BY D.Col1, D.Col1GroupCount, S.value
HAVING COUNT(DISTINCT D.Col2) = D.Col1GroupCount
)
SELECT D.Col1, D.Col2
FROM SelectedCol1Values S
JOIN Data D
ON D.Col1 = S.Col1
| Col1 | Col2 |
|---|---|
| PSPID_1 | POSID_10 |
| PSPID_1 | POSID_11 |
| PSPID_1 | POSID_12 |
| PSPID_3 | POSID_30 |
| PSPID_3 | POSID_31 |
| PSPID_3 | POSID_32 |
| PSPID_3 | POSID_33 |
WITH SelectedCol1Values AS (
SELECT DISTINCT D.Col1
FROM (
SELECT *, COUNT(*) OVER(PARTITION BY Col1) AS Col1GroupCount,
ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY Col2) AS RowNum
FROM Data D
) D
CROSS APPLY STRING_SPLIT(D.col3, ' ') S
WHERE S.value > ''
GROUP BY D.Col1, D.Col1GroupCount, S.value
HAVING COUNT(DISTINCT D.RowNum) = D.Col1GroupCount
)
SELECT D.Col1, D.Col2
FROM SelectedCol1Values S
JOIN Data D
ON D.Col1 = S.Col1
| Col1 | Col2 |
|---|---|
| PSPID_1 | POSID_10 |
| PSPID_1 | POSID_11 |
| PSPID_1 | POSID_12 |
| PSPID_3 | POSID_30 |
| PSPID_3 | POSID_31 |
| PSPID_3 | POSID_32 |
| PSPID_3 | POSID_33 |
| MATCH_3 | FALSE_33 |
| MATCH_3 | FALSE_33 |