Building sub-tree with correlated aggregate

Question

I apologize for the vague title. I couldn't think how best to summarize the problem. I have a hierarchical table (e.g., ID int, ParentID int) and need to generate a sub-tree for an ID. This is trivially done with a recursive CTE. The difficulty is that, for each node, I need to compute a running bitwise-OR of a set of corresponding values, and then bit-OR that result with the same value for the parent node. This means every node inherits its parent's bitmask and may set its own additional bits. I can compute this value in the anchor member of the CTE using OUTER APPLY and a technique mentioned in an earlier question I asked. Unfortunately, I can't compute it the same way in the recursive part of the CTE because it uses SUM and aggregates are not allowed there.

Is there a way to restructure this to do what I want?

declare @ID int
set @ID = 1

;with _Bits_(RowNum, BitMask) as
(
  select
    1,
    1
  union all select
    RowNum + 1,
    BitMask * 2
  from
    _bits_
  where
    RowNum < 31
),
_Tree_ as
(
  select
    a.ID,
    a.ParentID,
    b.BitMask
  from
    Tree a
    outer apply
    (
      select
        sum(distinct y.BitMask) as BitMask
      from
        BitValues x
        inner join _Bits_ y
          on (x.Value & y.BitMask) <> 0
      where
        x.ID = a.ID
    ) b
  where
    a.ID = @ID
  union all select
    a.ID,
    a.ParentID,
    c.BitMask | b.BitMask
  from
    Tree a
    inner join _Tree_ b
      on b.ID = a.ParentID
    outer apply
    (
      select
        sum(distinct y.BitMask) as BitMask
      from
        BitValues x
        inner join _Bits_ y
          on (x.Value & y.BitMask) <> 0
      where
        x.ID = a.ID
    ) c
)
select * from _Tree_

EDIT

If it helps to conceptualize the problem: the hierarchy is much like a directory structure, and the bitmasks are like permissions that are inherited from parent folders.

Example data

create table Tree (ID int primary key, ParentID int null foreign key references Tree (ID))

insert Tree values (1, null)
insert Tree values (2, 1)
insert Tree values (3, 1)

create table BitValues (ID int not null foreign key references Tree (ID), BitMask int not null)

insert BitValues values (1, 1)
insert BitValues values (2, 2)
insert BitValues values (2, 4)
insert BitValues values (3, 8)
insert BitValues values (3, 16)
insert BitValues values (3, 32)

For @ID 1, I would expect the query to return:

+----+----------+---------+
| ID | ParentID | BitMask |
+----+----------+---------+
|  1 |   NULL   |       1 |
|  2 |        1 |       7 |
|  3 |        1 |      57 |
+----+----------+---------+

Answer 1

declare @ID int;
set @ID = 1;

with extrarows as
(
   select t.id, null as parent, v.BitMask as total
   from tree t
   join BitValues v on t.id = v.id
   where t.id = @ID

   union all 

   select t.id, r.id, v.BitMask | r.total
   from extrarows r
   join Tree t on r.id = t.parentid
   join BitValues v on t.id = v.id
)
select id, parent, 
  MAX(total & 1) +
  MAX(total & 2) +
  MAX(total & 4) +
  MAX(total & 8) +
  MAX(total & 16) +
  MAX(total & 32) +
  MAX(total & 128) +
  MAX(total & 256) +
  MAX(total & 512) +
  MAX(total & 1024) +
  MAX(total & 2048)  -- more if you want em.
     as BitMask 
from extrarows   
group by id, parent

Some notes:

• I make an assumption the incoming @id is the "root" of the tree. (Feel free to crawl up the tree to find the starting bit mask of the root if this does not meet your needs.)

• While summing MAX of the bits does work it might not be performant for large bit strings over many records. I don't know how many bits you have but it is less then 16 or so it should be fine -- like to hear about your findings.

• To improve performance switch to a custom C# aggregate.

Answer 2

A slight refinement (IMO) of Hogan's answer:

declare @ID int;
set @ID = 1;

with _Bits_(RowNum, BitMask) as
(
  select
    1,
    1
  union all select
    RowNum + 1,
    BitMask * 2
  from
    _bits_
  where
    RowNum < 31
),
extrarows as
(
   select t.id, null as parent, v.BitMask as total
   from tree t
   join BitValues v on t.id = v.id
   where t.id = @ID

   union all 

   select t.id, r.id, v.BitMask | r.total
   from extrarows r
   join Tree t on r.id = t.parentid
   join BitValues v on t.id = v.id
)
select a.id, a.parent, sum(distinct y.BitMask) as BitMask
from extrarows a
  inner join _Bits_ y
    on (a.total & y.BitMask) <> 0  
group by a.id, a.parent