SQL Aggregate logical operations-CodePudding

Is there a way in T-SQL to aggregate with logical conditions on fields which do not represent single bits?

For example, given this data:

CREATE TABLE #Example       (category int, flags int)
INSERT INTO #Example VALUES (1,            1) -- Binary 0001
INSERT INTO #Example VALUES (1,            2) -- Binary 0010

I'd like a query that does something similar to:

SELECT AND(flags) FROM #Example
GROUP BY Category
-- Expect the result to be 0 because 0001 AND 0010 = 0000

SELECT OR(flags) FROM #Example
GROUP BY Category
-- Expect the result to be 3 because 0010 OR 0001 = 0011

On a single bit field I know you can convert to int and then use max and min, but this would only work on single bit values so doesn't solve the problem.

Additionally, while I'm trying to work this out in SQL, ultimately I'd like to do it in a EF/Linq expression, which might have a way of doing this that SQL doesn't?

CodePudding user response：

SQL Server does not have such an aggregate function. You could create one using SQLCLR.

But an easier option is to split out each bit and aggregate separately, then add them back together.

For OR

SELECT
  MAX(flags & 1)  
  MAX(flags & 2)  
  MAX(flags & 4)  
  MAX(flags & 8)
FROM #Example
GROUP BY Category

For AND

SELECT
  MIN(flags & 1)  
  MIN(flags & 2)  
  MIN(flags & 4)  
  MIN(flags & 8)
FROM #Example
GROUP BY Category

db<>fiddle