ID               Node                  ParentID
1                 A                        0
2                 B                        1
3                 C                        1
4                 D                        2
5                 E                        2
6                 F                        3
7                 G                        3
8                 H                        3 
9                 I                        4
10                J                        4
11                K                        10
12                L                        11

Create table #RecursTest
(
ID integer,
Node varchar(10),
ParentId integer
)

insert into #RecursTest(Id,Node,ParentID) values(1,                 'A',                        0)
insert into #RecursTest(Id,Node,ParentID) values(2,                 'B',                        1)
insert into #RecursTest(Id,Node,ParentID) values(3,                 'C',                        1)
insert into #RecursTest(Id,Node,ParentID) values(4,                 'D',                        2)
insert into #RecursTest(Id,Node,ParentID) values(5,                 'E',                        2)
insert into #RecursTest(Id,Node,ParentID) values(6,                 'F',                        3)
insert into #RecursTest(Id,Node,ParentID) values(7,                 'G',                        3)
insert into #RecursTest(Id,Node,ParentID) values(8,                 'H',                        3)
insert into #RecursTest(Id,Node,ParentID) values(9,                 'I',                        4)
insert into #RecursTest(Id,Node,ParentID) values(10,                'J',                        4)
insert into #RecursTest(Id,Node,ParentID) values(11,                'K',                        10)
insert into #RecursTest(Id,Node,ParentID) values(12,                'L',                        11)



go
with cteRecurs(AncestorId,Id,Node,ParentId)
as
(
    Select Id,Id,Node,ParentId
      from #RecursTest
    union all
    Select cteRecurs.AncestorId,#RecursTest.Id,#RecursTest.Node,#RecursTest.ParentId
      from cteRecurs,
           #RecursTest
     where #RecursTest.ParentId = cteRecurs.Id
)

select AncestorId,count(*)-1 From cteRecurs
group by AncestorId