In a few of the tables I'm working with, I have some bit-mapped values. In other words, there is a numeric (integer) column and the values are all powers of two (1, 2, 4, 8, etc.) and each power of two has a different meaning. When I want to summarize this data, I want to perform a bitwise OR on the values for a particular entity, for example if a particular entity has records with values of 1, 2, and 8, then I want to show the value 11. SQL Server has a bitwise OR operator (|), but it doesn't have a bitwise OR aggregate function. In the simple example above, you may notice that 1 | 2 | 8 == 1 + 2 + 8 so it would be good if we could use the SUM aggregate function SUM ( sourcecolumn ), but that won't work if any of the values are duplicated: 1 | 1 | 2 | 8 != 1 + 1 + 2 + 8 . Fortunately, you can use the SUM function to add up only the DISTINCT values: SUM ( DISTINCT sourcecolumn ). This, then, can be used as a bitwise OR aggregate function, provided the values in the column are powers of two.
