Aggregate bitwise-OR in a subquery

Question

Given the following table:

CREATE TABLE BitValues ( n int )

Is it possible to compute the bitwise-OR of n for all rows within a subquery? For example, if BitValues contains these 4 rows:

+---+
| n |
+---+
| 1 |
| 2 |
| 4 |
| 3 |
+---+

I would expect the subquery to return 7. Is there a way to do this inline, without creating a UDF?

Solution 1

WITH    Bits
          AS ( SELECT   1 AS BitMask
               UNION ALL
               SELECT   2
               UNION ALL
               SELECT   4
               UNION ALL
               SELECT   8
               UNION ALL
               SELECT   16
             )
    SELECT  SUM(DISTINCT BitMask)
    FROM    ( SELECT    1 AS n
              UNION ALL
              SELECT    2
              UNION ALL
              SELECT    3
              UNION ALL
              SELECT    4
              UNION ALL
              SELECT    5
              UNION ALL
              SELECT    6
            ) AS t
            JOIN Bits ON t.n & Bits.BitMask > 0

Solution 2

I see this post is pretty old and there are some useful answers but this is a pretty crazy straight forward method...

Select  
    SUM(DISTINCT(n & 0x01)) +
    SUM(DISTINCT(n & 0x02)) +
    SUM(DISTINCT(n & 0x04))
    as OrN
From BitValues

Solution 3

A simple solution which is a mix of @AlexKuznetsov's and @Andomar's solutions.
The bit mask is generated by a recursive Common Table Expression, but in a simpler way than in @Andomar's solution.
The bits are then summed just like in @AlexKuznetsov's solution.
In this example I assume a 16 bits mask is required, hence the 65536 limit. You can indicate a N-bits mask by changing 65536 to 2^N.

WITH Bits AS
(
    SELECT 1 BitMask
    UNION ALL
    SELECT 2 * BitMask FROM Bits WHERE BitMask < 65536 -- recursion
)
SELECT SUM(DISTINCT BitMask)
FROM
    (SELECT 1 n
    UNION ALL
    SELECT 2 n
    UNION ALL
    SELECT 4 n
    UNION ALL
    SELECT 3 n) t
    INNER JOIN Bits ON t.n & Bits.BitMask > 0

Solution 4

Preparations:

if object_id(N'tempdb..#t', N'U') is not null drop table #t;
create table #t ( n int );
insert into #t values (1), (2), (4), (3);

Solution:

select max(n & 8) + max(n & 4) + max(n & 2) + max(n & 1) from #t;

Solution 5

You can use a variable and do a "bitwise or" (|) for each row:

declare @t table (n int)
insert @t select 1 union select 2 union select 4

declare @i int
set @i = 0

select  @i = @i | n
from    @t

select @i

This prints 7. Note that assigning variables in a select is not officially supported.

In a more strictly SQL way, you can create a table with one row for each bit. This table would have 31 rows, as the 32nd bit is a negative integer. This example uses a recursive CTE to create that table:

declare @t table (n int)
insert @t select 1 union select 2 union select 3

; with bits(nr, pow) as 
(
    select  1
    ,       1
    union all
    select  nr + 1
    ,       pow * 2
    from    bits
    where   nr <= 30
)
select  sum(b.pow)
from    bits b
where   exists
        (
        select  *
        from    @t t  
        where   b.pow & t.n > 0
        )

This sums the bits where any bit in the source table is set.

View more solutions