Clever Geek Handbook

Aggregate bit field values ​​with binary OR

I have a table with int values ​​that are used as bit fields (where each bit is a flag).

Now I would like to combine them with a binary operation (OR in my case) so that:

SELECT 1 AS bitfield
INTO #TABLE
UNION ALL SELECT 1 + 2 + 8 + 32
UNION ALL SELECT 2 + 128
UNION ALL SELECT 2 + 32

SELECT AND_AGGR(bitfield) -- Invalid because AND_AGGR doesn't exist
FROM #TABLE

DROP #TABLE

will result in the value 171

What would be a good way to do this, which hopefully doesn't take a lot |

and MAX

(but if you do, you should)?

I myself use MS SQL Server 2008, but also interesting solutions on other servers.

+1


source to share


2 answers


If you expect the result 171

, do you mean the binary OR

not AND

?

Anyway, this solution concatenates the values ​​into a variable:

SELECT 1 AS bitfield
INTO #TABLE
UNION ALL SELECT 1 + 2 + 8 + 32
UNION ALL SELECT 2 + 128
UNION ALL SELECT 2 + 32

DECLARE @i int = 0

SELECT @i = @i | bitfield
FROM #TABLE

SELECT @i

DROP TABLE  #table


This may not suit your requirements if you want to group the aggregation with a different field.

It is also unlikely to perform well on a large table.

+1


source


In MySQL and PostgreSQL, you can use BIT_OR

.

I don't think SQL Server has this cumulative feature.



You can do it with more MAX

and &

as you said:

MAX(x & 1) + MAX(x & 2) + ... + MAX(x & 128)
+2


source






More articles:


All Articles