By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
Msg 195 Level 15 State 10 Line 1
'version' is not a recognized built-in function name.
CREATE TABLE test (
col1 VARCHAR(50),
col2 VARCHAR(50),
col3 VARCHAR(255)
);
INSERT INTO test (col1, col2, col3) 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', 'ABGS EROF'),
('PSPID_3', 'POSID_31', 'ABGS EROF'),
('PSPID_3', 'POSID_32', 'FREI ABGS EROF'),
('PSPID_3', 'POSID_33', 'FREI ABGS');
9 rows affected
WITH filtered_groups AS (
SELECT col1
FROM test
GROUP BY col1
HAVING COUNT(*) = SUM(CASE WHEN col3 LIKE '%ABGS%' THEN 1 ELSE 0 END)
)
SELECT t.col1, t.col2
FROM test t
JOIN filtered_groups fg ON t.col1 = fg.col1
ORDER BY t.col1, t.col2;
| 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 |