Recursive CTE with subtree Count (SQL Server 2005+)

Here is a sample code with recursive CTE along the count of the subtree (children) to solve a question “Populate tree from self joined table with number of child records” at ASP.NET forum:

create table SampleRecursive (id int, name varchar(50),parentid int)

insert into SampleRecursive values(1,‘A’,0)
insert into SampleRecursive values(2,‘B’,0)
insert into SampleRecursive values(3,‘A.1’,1)
insert into SampleRecursive values(4,‘A.2’,1)
insert into SampleRecursive values(5,‘A.1.1’,3)
insert into SampleRecursive values(6,‘B.1’,2)
insert into SampleRecursive values(7,‘B.1.1’,6)
insert into SampleRecursive values(8,‘B.1.1.1’,7)
insert into SampleRecursive values(9,‘A.1.2’,3)

—-with mycte
—- id, name, parentid, 0 as lvl from SampleRecursive sr
—-where sr.parentid=0
—-union all
—-select,,s.parentid,lvl+1 from SampleRecursive s
—-inner join mycte m on

—-select * from mycte
—-order by name

;with mycte
id, name, parentid, 0 as lvl from SampleRecursive sr
where sr.parentid=0
union all
select,,s.parentid,lvl+1 from SampleRecursive s
inner join mycte m on

SELECT, name, parentid, (MAX(subcount)-1) [NUMBER OF CHILD RECORD FOR THIS LEVEL] FROM mycte m1
INNER JOIN (select id, COUNT(*) subCount from mycte GROUP BY id) m2 ON
GROUP BY, name, parentid


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s