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?
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