DECLARE @data TABLE (
id INT,
field1 CHAR(1),
field2 CHAR(1),
field3 CHAR(1)
)
INSERT INTO @data ( id, field1, field2, field3 )
VALUES
(1, 'Y', 'N', 'N' ),
(1, 'N', 'N', 'N' ),
(2, 'Y', 'N', 'Y' ),
(2, 'Y', 'N', 'N' ),
(3, 'N', 'Y', 'N' ),
(3, 'N', 'Y', 'N' ),
(3, 'N', 'Y', 'N' )
--SELECT * FROM @data
; WITH cte
AS (
SELECT A.id, COUNT(*) AS NbRows
, (SELECT COUNT(*) FROM @data WHERE id = A.id AND field1 = 'Y') AS F1
, (SELECT COUNT(*) FROM @data WHERE id = A.id AND field2 = 'Y') AS F2
, (SELECT COUNT(*) FROM @data WHERE id = A.id AND field3 = 'Y') AS F3
FROM @data AS A
GROUP BY A.id
)
SELECT cte.id
FROM cte
WHERE cte.NbRows = cte.F1 OR cte.NbRows = cte.F2 OR cte.NbRows = cte.F3
DELETE FROM @data
INSERT INTO @data ( id, field1, field2, field3 )
VALUES
(1, 'Y', 'N', 'N' ),
(2, 'Y', 'N', 'Y' ),
(2, 'Y', 'N', 'N' ),
(3, 'N', 'Y', 'N' ),
(3, 'N', 'Y', 'N' ),
(3, 'N', 'Y', 'N' )
--SELECT * FROM @data
; WITH cte
AS (
SELECT A.id, COUNT(*) AS NbRows
, (SELECT COUNT(*) FROM @data WHERE id = A.id AND field1 = 'Y') AS F1
, (SELECT COUNT(*) FROM @data WHERE id = A.id AND field2 = 'Y') AS F2
, (SELECT COUNT(*) FROM @data WHERE id = A.id AND field3 = 'Y') AS F3
FROM @data AS A
GROUP BY A.id
)
SELECT cte.id
FROM cte
WHERE cte.NbRows = cte.F1 OR cte.NbRows = cte.F2 OR cte.NbRows = cte.F3
CREATE TABLE mytable(
Id INTEGER NOT NULL
,Field1 VARCHAR(4) NOT NULL
,Field2 VARCHAR(2) NOT NULL
,Field3 VARCHAR(2) NOT NULL
);
INSERT INTO mytable(Id,Field1,Field2,Field3) VALUES (1,'Y','N','N');
INSERT INTO mytable(Id,Field1,Field2,Field3) VALUES (1,'N','N','N');
INSERT INTO mytable(Id,Field1,Field2,Field3) VALUES (2,'Y','N','Y');
INSERT INTO mytable(Id,Field1,Field2,Field3) VALUES (2,'Y','N','N');
INSERT INTO mytable(Id,Field1,Field2,Field3) VALUES (3,'N','Y','N');
INSERT INTO mytable(Id,Field1,Field2,Field3) VALUES (3,'N','Y','N');
INSERT INTO mytable(Id,Field1,Field2,Field3) VALUES (3,'N','Y','N');
select ca.id, ca.all_y
from mytable
cross apply (
values
(id, 'field1', field1)
, (id, 'field2', field2)
, (id, 'field3', field3)
) ca (id, all_y, fieldval)
group by ca.id, ca.all_y
having min(fieldval) = max(fieldval) and max(fieldval) = 'Y'
;
+-----+--------+
| id | all_y |
+-----+--------+
| 2 | field1 |
| 3 | field2 |
+-----+--------+
Open in new window