Try this, possibly not the most efficient, i suspect that a hierarchyId-esque based query may be faster over a large dataset , but should do

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