Count descendant rows in a table tree

We have a table with tree structure like this:

Id  Desc  ParentID
===
A    DescrA   NULL
B    DescrB   A
C    DescrC   A
D    DescrD   C
E    DescrE   C
F    DescrF   E

We need a query that returns the number of descendants (including subdescendants) of a specific ID, something like:

select count(descendants) from Tablex where id='A' --result = 5
select count(descendants) from Tablex where id='B' --result = 0
select count(descendants) from Tablex where id='C' --result = 3
select count(descendants) from Tablex where id='E' --result = 1

We have seen it would be made 'easily' with CTE but couldnt get the gist of it...

Answers


declare @T table
(
  Id char(1),
  ParentId char(1)
);

insert into @T values
('A', NULL),
('B', 'A'),
('C', 'A'),
('D', 'C'),
('E', 'C'),
('F', 'E');

declare @ParentId char(1);
set @ParentId = 'A';

with C as
(
  select Id, ParentId
  from @T
  where ParentId = @ParentId
  union all
  select T.Id, T.ParentId
  from @T as T
    inner join C
      on T.ParentId = C.Id
)
select count(*)
from C;

Need Your Help

Android In-app purchases with Phonegap

android paypal cordova in-app-purchase in-app-billing

Is there any way to implement Android In-app purchases with Phonegap? I can't find any information abt this issue. For example I can do IAP on iOS using InAppPurchaseManager Phonegap plugin. But th...

return the most frequent value within each group

sql sas proc-sql

Trying to create a table that displays the most frequent ndc# for a given patient(pat_seqno). My having clause have not worked thus far and this is my most recent attempt, which returns an error t...