Query correct data from the MS SQL Server 2005 (2008) database to fill the treeview

Rating: 59 user(s) have rated this article
Posted by: sergey, on 04/09/2008, in category "ASP.NET"
Views: this article has been read 32719 times
Location: United Kingdom

Abstract: Sometimes it is difficult to query data in correct order for the tree. I show one of the way you can correctly present this data and your tree will be filled without any problems.

declare @t table ( ID int, Parent_ID int, Name varchar(255))

insert into @t (ID, Parent_ID, Name)
select NULL, NULL, 'Something 1' union all
select 1, NULL, 'Something 1' union all
select 2, NULL, 'Something 2' union all
select 8, 3, 'Something 3' union all
select 3, 1, 'Something 3' union all
select 4, 1, 'Something 4' union all
select 5, 2, 'Something 5' union all
select 6, 2, 'Something 6' union all
select 7, 6, 'Something 7'

;with c(ID, Parent_ID, newName)
as
(
select ID, Parent_ID, convert(varchar(255),STR(ISNULL(ID,0)))
from @t
where ISNULL(Parent_ID,0) = 0
union all
select t.ID, t.Parent_ID, convert(varchar(255), c.newName + ' - ' + convert(varchar(255),STR(ISNULL(t.ID,0))))
from @t t
inner join c on t.Parent_ID = c.ID
)

select * from c order by newName