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

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