Performing a bitwise sum

As part of a complex aggregate I want to know the bitwise sum of some data, i.e. if I have rows with values 1,1,1,2,2,8 the bitwise sum is 11. In this case the values are all exac开发者_StackOverflowt powers of two (single bits), so I can hack around it by grouping and summing over the groups (obviously this example is a bit tortured compared to the real query):

select SUM(y.test)
from (
    select x.test
    from ( -- garbage test data
        select 1 as [test]
        union all select 1
        union all select 1
        union all select 2
        union all select 2
        union all select 8) x
group by x.test) y

but is there a clean way to perform a bitwise sum in [T]SQL?

If all of your test values are single bits as in your example (1, 2, 8) - simply use SUM(DISTINCT col) in your query.

Hope that helps.

(For reference: http://msdn.microsoft.com/en-us/library/ms187810.aspx)

Its a bit convoluted but this does what you're after (note that ive included bit's up to 128, you may need to go higher, or may not need to go any higher than 8).

with data(i)
AS
(
    select 1 
        union all select 1
        union all select 1
        union all select 2
        union all select 2
        union all select 8
)
SELECT MAX(i & 1) +
MAX(i & 2) +
MAX(i & 4) +
MAX(i & 8) +
MAX(i & 16) +
MAX(i & 32) +
MAX(i & 64) +
MAX(i & 128)
from data

which can obviously be converted to a UDF if you so desire.

You have the operator | which performs bitwise or for 2 operands. It is possible to solve your problem using a cursor and this operator.

Edit yes I mixed up and and or, fixed.

For single bits the SUM(DISTINCT col) is best answer but for multiple bits like 3 (=1 | 2) this doesn't work and best and quicker answer would be like this:

SELECT SUM(DISTINCT X.Text & T.B) FROM X cross join (values (1),(2),(4),(8),(16),(32),(64),(128),(256),(512)) T(B)