Performing a bitwise sum

Question

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

Answer 1

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.

Answer 2

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)