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: http://forums.asp.net/t/1671284.aspx/1?Populate+tree+from+self+joined+table+with+number+of+child+records+

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
—-as
—-(select
—- id, name, parentid, 0 as lvl from SampleRecursive sr
—-where sr.parentid=0
—-union all
—-select s.id, s.name,s.parentid,lvl+1 from SampleRecursive s
—-inner join mycte m on m.id=s.parentid
—-)

—-select * from mycte
—-order by name

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

SELECT m1.id, 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 m1.id=m2.id
GROUP BY m1.id, name, parentid
ORDER BY m1.name

Advertisements


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s