Reputation: 9607
Is there any way of grouping the results of a select by individual bits set in a column? That is, ignoring combinations of various bits (otherwise a simple group by column
would suffice).
For example, assuming a table has the values 1 through 10 for the group-by column exactly once (presented below with binary representation to simplify the construction/verification of the following group-by statement result):
1 0001
2 0010
3 0011
4 0100
5 0101
6 0110
7 0111
8 1000
9 1001
10 1010
the group-by effect I want to achieve would look like:
select <bit>, count(*), group_concat(<column>) from <table> group by <...>
0 5 1,3,5,7,9
1 5 2,3,6,7,10
2 4 4,5,6,7
3 3 8,9,10
assuming the first bit is "bit 0".
I'm using MySQL at the moment, so ideally a solution should work there; but I'd be interested in other RDBMS solutions, if any.
Upvotes: 4
Views: 395
Reputation: 1271241
You would need to split the values up and then reaggregate. Something like this:
select n.n, group_concat(col1)
from t join
(select 0 as n union all select 1 union all select 2 union all select 3
) n
on (1 << n) & bits > 0
group by n;
Upvotes: 2