Aggregate bitwise-OR in a subquery *with partitioning*?

Question

I've been looking at how to do a bitwise-OR aggregation in TSQL in a subquery, and the answers given in a popular question don't seem to maintain partitioning. Is there any way for me to partition?

Let's say we have the following data:

id | someCount | someFlags
1  | 2         | 0
1  | 3         | 2
1  | 0         | 1
2  | 1         | 4
2  | 5         | 0
2  | 1         | 1

I can get the SUM of the someCount field partitioned by id without losing any of the original rows like so:

SELECT [testSum] = SUM([someCount]) OVER (PARTITION BY [id]) FROM myTable

Is there a way to do this with a bitwise OR operation? so I'd get:

id | value
1  | 3
1  | 3
1  | 3
2  | 5
2  | 5
2  | 5

Answer 1

One way would be as below. Extend it out to the number of bit positions that you care about.

Having to repeat the PARTITION BY [id] is a bit tedious but I would expect SQL Server to only do the partitioning work once and calculate all the MAX expressions inside the same aggregate operator.

SELECT *, 
        MAX(someFlags & 1) OVER (PARTITION BY [id])  +
        MAX(someFlags & 2) OVER (PARTITION BY [id])  +
        MAX(someFlags & 4) OVER (PARTITION BY [id])  +
        MAX(someFlags & 8) OVER (PARTITION BY [id])
FROM (VALUES
(1 , 2 , 0),
(1 , 3 , 2),
(1 , 0 , 1),
(2 , 1 , 4),
(2 , 5 , 0),
(2 , 1 , 1)
)V(id ,someCount ,someFlags)