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

Rating: 59 user(s) have rated this article Average rating: 5.0
Posted by: sergey, on 04/09/2008, in category "ASP.NET"
Views: this article has been read 12570 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
 

How would you rate this article?

User Feedback

Post your comment:
Name:
E-mail:
Comment:
Insert Cancel