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

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;

Answer 2

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 3

For me that is the best solution.

declare @res int
set @res=0    
SELECT  @res=@res|t.n
    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

Answer 4

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

Answer 5

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

Answer 6

This is an alternative, without WITH (hurrah!!!):

    select sum(distinct isnull(n & BitMask, 0)) as resultvalue
    from 
    (
          SELECT    1 AS n
          UNION ALL
          SELECT    2
          UNION ALL
          SELECT    4
          UNION ALL
          SELECT    3
    ) t
    INNER JOIN (SELECT 0 BitMask union all SELECT 1 union all SELECT 2 union all SELECT 4 union all SELECT 8 union all SELECT 16 union all SELECT 32 union all SELECT 64 union all SELECT 128 union all SELECT 256 union all SELECT 512 union all SELECT 1024 union all SELECT 2048 union all SELECT 4096 union all SELECT 8192 union all SELECT 16384 union all SELECT 32768 union all SELECT 65536) Bits -- = SELECT POWER(2, 16)
    ON n & BitMask = BitMask;

Also consider a Group By example:

 -- Setup temp table to produce an example --
 create table #BitValues
 (
    id int identity(1,1)
    ,value int
    ,groupby varchar(10)
 )

 insert into #BitValues
 SELECT    1 AS value, 'apples'
          UNION ALL
          SELECT    2, 'apples'
          UNION ALL
          SELECT    4, 'apples'
          UNION ALL
          SELECT    3, 'apples'

 -- Bit operation: --
  select groupby, sum(distinct isnull(value & BitMask, 0)) as tempvalue
  from #BitValues
  INNER JOIN (SELECT 0 BitMask union all SELECT 1 union all SELECT 2 union all SELECT 4 union all SELECT 8 union all SELECT 16 union all SELECT 32 union all SELECT 64 union all SELECT 128 union all SELECT 256 union all SELECT 512 union all SELECT 1024 union all SELECT 2048 union all SELECT 4096 union all SELECT 8192 union all SELECT 16384 union all SELECT 32768 union all SELECT 65536) Bits -- = SELECT POWER(2, 16)
      ON value & BitMask = BitMask
  group by groupby

The first example is meant to be slower than WITH. However when you use GroupBy with some other data, the queries are largely the same cost-wise.

Another way to do this is

    select 
    groupby
      ,max(case when n & 1 = 1 then 1 else 0 end)
            +
        max(case when n  & 2 = 2 then 2 else 0 end)
            +
        max(case when n & 4 = 4 then 4 else 0 end)  
            +
        max(case when n & 8 = 8 then 8 else 0 end)
            +
        max(case when n & 16 = 16 then 16 else 0 end)
            +
        max(case when n & 32 = 32 then 32 else 0 end)
            +
        max(case when n & 64 = 64 then 64 else 0 end)
            +
        max(case when n & 128 = 128 then 128 else 0 end)
            +
        max(case when n & 256 = 256 then 256 else 0 end)
            +
        max(case when n & 512 = 512 then 512 else 0 end)
            +
        max(case when n & 1024 = 1024 then 1024 else 0 end)
            as NewDNC
    from #BitValues
    group by groupby;

It's a bit worse because of repetition in code, a bit more readable and similar in execution cost.