All possible bit combinations

Question

Suppose I have a table:

id      val
 0       1
 1       2
 2       4

I am looking for SQL that will return me all the bitwise column combinations valthat are possible. So, for example, from the above table, I would expect the following possible combinations val:

1  -- from original table id: 0
2  -- from original table id: 1
3  -- combination of id: 0 & 1
4  -- from original table id: 2
5  -- combination of id: 0 & 2
6  -- combination of id: 1 & 2
7  -- combination of id: 0 & 1 & 2

Answer 1

This query returns all sum (val) combinations, perhaps not what you are looking for . If you are looking for bitwise and you should use t.val & cur.val(thanks @Andrew Deighton) (I did not create table t, instead I build it dynamically. You won’t need it)

with t as
    (select 0 as id, 1 as val union all
     select 1 as id, 2 as val union all
     select 2 as id, 4 as val ),
 cur as 
    ( select id, val  from t union all
      select t.id, t.val + cur.val from cur join t on cur.id>t.id)
 select val
  from cur
order by val

OUTPUT

val
1
2
3
4
5
6
7

If you add a new entry with val = 8, you will get:

val
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

Answer 2

aTable -

CREATE TABLE #combo (z bigint);

DECLARE @CNT integer;

SELECT @CNT = COUNT(0) FROM aTable;

DECLARE @I int;
SET @I = 0
WHILE @I < POWER(2, @CNT)
BEGIN
    INSERT #combo VALUES(@I);
    SET @I = @I + 1;
END

--select * from #combo;

;with cte as (select *, row_number() over (order by id) as rn from aTable),
    combs as (select c1.z,cte.rn, cte.val,  POWER(2, cte.rn -1) & c1.z as flag  from cte join #combo c1 ON POWER(2, cte.rn -1) & c1.z != 0 )


select *, row_number() over (partition by z order by rn) as lev, val final_val into #perms from combs  order by z,rn;

--select * from #perms;

DECLARE @depth as integer;
SET @depth = 2;



WHILE EXISTS (SELECT 0 FROM #perms WHERE lev = @depth)
begin

    UPDATE #perms 
    SET #perms.final_val = p.final_val & #perms.val
FROM
  #perms
INNER JOIN
  #perms p
ON
  p.lev = #perms.lev - 1 AND p.z = #perms.z 

  set @depth = @depth + 1;

end

select #perms.z,#perms.final_val from #perms join (SELECT p.z, max(p.lev) maxz FROM #perms p GROUP BY z) DQ ON #perms.lev = dq.maxz and dq.z = #perms.z order by #perms.z;

DROP TABLE #combo;
DROP TABLE #perms;

---

with t as
    (select * from atable),
 cur as 
    ( select id, val  from t union all
      select t.id, t.val & cur.val from cur join t on cur.id>t.id)
 select id,val
  from cur
order by id