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?

Answer 1

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.

Answer 2

Are you looking for something like this?

EDIT: As noted in other comments, this answer was based on the assumption that the BitValues table would only contain powers of 2. I tried to read between the lines of the question and infer a use for the inline subquery.

declare @BitValues table (
    n int
)

declare @TestTable table (
    id int identity,
    name char(10),
    BitMappedColumn int
)

insert into @BitValues (n)
    select 1 union all select 2 union all select 4

insert into @TestTable
    (name, BitMappedColumn)
    select 'Joe', 5 union all select 'Bob', 8

select t.id, t.name, t.BitMappedColumn
    from @TestTable t
        inner join (select SUM(n) as BitMask from @BitValues) b
            on t.BitMappedColumn & b.BitMask <> 0

Answer 3

This seems to work:

SELECT SUM(DISTINCT n) from BitValues

Answer 4

Your best bet for a readable and re-usable solution would be to write a a custom CLR Aggregate to perform bitwise or. A tutorial for creating this type of operation can be found here: http://msdn.microsoft.com/en-us/library/91e6taax(VS.80).aspx

Answer 5

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