GROUP BY Bit in Integer

I have a table where each row contain a unique individual and it's scrap code. The scrap code is an integer where each of the 32 bits represent a unique scrap cause and each individual may have more than one scrap cause.

As an example: the indidual may be both too heavy and too high

Lets say these two scrap causes are represented by bit 0 and bit 1.

That again converts to the integer value 1 and 2. If both bits are true the value of the integer for that row/individual is 3. I use a support table that contain each bit (representetd as an integer value) and a description.

ScrapCode(ScrapCode, Description)

with upto 32 rows.

With this it is easy to decode the scrap code for a selected individual into separate causes, but that is for one individual.
Now I would like to create a report that count codes based on the unique scrap codes in the scrap code table. In other words I would like to group by each bit in the integer.

Table(Id, date, ScrapCode)

Select Count(*) from Table

WHERE ScrapCode <> 0

Group By "Bit"

Any suggestions?

can u supply an example of the expected outcome.|||

One way to do it is to take advantage of the POWER function, the bitwise '&' operator and a table of numbers. I mocked up the data with this:

declare @.aTable table
( rid integer,
scrapCode integer
)

insert into @.aTable
select iter,
-1000000000 + 2147483646*dbo.rand()
from small_iterator (nolock)

The definition of the SMALL_ITERATOR and DBO.RAND() objects can be found here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1330536&SiteID=1

I computed the results with this:

Select iter-1 as [Cause],
Count(*) as[Cause Count]
from @.aTable
inner join small_iterator (nolock)
on iter <= 32
WHERE ScrapCode <> 0
and ( iter < 32 and
ScrapCode & Power (2, iter-1) > 0 or
iter = 32 and
ScrapCode < 0
)
group by iter-1
order by iter-1

/*
Cause Cause Count
-- --
0 16333
1 16494
...
30 16470
31 15336
*/

|||

Found it myself after some trial and error:

Code Snippet

SELECT v.ScrapCode, COUNT(p.Snr) AS NoOfScrap

FROM [table] p, [ScrapCodes] v

WHERE p.date > '2007-04-20'

AND p.Scrapcode <> 0

AND p.ScrapCode & v.ScrapCode <> 0

GROUP BY v.ScrapCode

Output:

1 15

2 250

4 5

8 98

512 23

....