Aggregate function for bit-wise OR [duplicate]

Question

In SQL Server 2022, is there an aggregate function for doing a bit-wise OR against a particular column?

-- Example table, data and output to show the different flags
DECLARE @TEST TABLE (FLAGS TINYINT)

INSERT INTO @TEST VALUES (1), (2), (3), (9), (10)

SELECT FLAGS, FLAGS & 1, FLAGS & 2, FLAGS & 4, FLAGS & 8 
FROM @TEST

-- Example output of the above...
FLAGS, FLAG1, FLAG2, FLAG4, FLAG8
1      1      0      0      0
2      0      2      0      0
3      1      2      0      0
9      1      0      0      8
10     0      2      0      8

What I would LIKE to be able to do is find all the flags which have been set

-- Non-existent aggregate to demonstrate what I want
SELECT BIT_OR(FLAGS) FROM @TEST

-- Example output from the above data
FLAGS
11

If there isn't an aggregate (and I can't find one so I guess there isn't)... how can I achieve this?

Charlieface · Accepted Answer · 2025-10-23 13:18:59Z

3

You can use MAX on a single-bit column to simulate BIT_OR. Then do a bitwise | to put them all into one column.

SELECT MAX(FLAGS & 1) | MAX(FLAGS & 2) | MAX(FLAGS & 4) | MAX(FLAGS & 8)
FROM @TEST;

MIN will simulate BIT_AND instead.

db<>fiddle

answered Oct 23 at 13:18

Charlieface

78.5k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

freefaller Oct 24 at 7:56

I was hoping for an aggregate so it wasn't necessary to state each individual bit... especially when I actually have an unknown quantity at time of usage. Ah well... it works, even if it's not ideal... thank you Charlie

Collectives™ on Stack Overflow

Aggregate function for bit-wise OR [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related