By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.YourTable (Col1 char(2),
Col2 char(4));
INSERT INTO dbo.YourTable (Col1,Col2)
VALUES('aa',NULL),
('aa','date'),
('aa',NULL),
('bb','date'),
('cc',NULL),
('cc',NULL),
('dd','date'),
('dd','date');
8 rows affected
WITH CTE AS(
SELECT Col1,
Col2,
COUNT(CASE Col2 WHEN 'date' THEN NULL ELSE 1 END) OVER (PARTITION BY Col1) AS NonDateCount
FROM dbo.YourTable)
SELECT Col1,
Col2
FROM CTE
WHERE NonDateCount = 0;
| Col1 | Col2 |
|---|---|
| bb | date |
| dd | date |
| dd | date |
Warning: Null value is eliminated by an aggregate or other SET operation.
DROP TABLE dbo.YourTable;