SQL Aggregate bit columns to single bit result

Question

I have some rows in the following format:

I want a SQL query which will group the above rows by EntityId and aggregate the bit columns in the following way:

• Submitted: 0 if any row for a given EntityId is 0, else 1
• Reviewed: 0 if any row for a given EntityId is 0, else 1
• Query: 1 if any row for a given EntityId 1, else 0

I know I can do this by casting the bit column as an int and using Min/Max but it feels like a bit of a hack. I think I am having a slow day and missing the obvious solution...

What is the best way to do this?

I am using SQL Server 2008 R2, although a general SQL method would be best.

Update:

The desired result set for the rows above would be:

Answer 1

I think casting to an int is probably best overall as there are no bitwise aggregates, anything else will also be "hacky".

For fun this should work without casting the bit fields;

select
  EntityId,
  1 ^ count(distinct nullif(Submitted, 1)),
  1 ^ count(distinct nullif(Reviewed, 1)),
  count(distinct nullif(Query, 0))
from t
group by EntityId