Order table items in a "TreeView" way. How?
-
Imagine I have a table with these items
Id ParentId Name Position
> 1 0 "Items" 1
> 2 0 "Standings" 3
> 3 0 "Test" 2
> 4 2 "Liverpool" 2
> 5 2 "AC Milan" 1
> 6 1 "Itemno1" 1
> This menu should in layout come in this order:
> Items
> ----Itemno1
> Test
> Standings
> ----AC Milan
> ----Liverpool
How I can do this in Store Procedure if I have table with this data?
I can manage this with recursion in C# but in MS SQL 2005 in order to save processor time?
Thanks
Alex
-
refer the below like will help you
http://www.sqlservercentral.com/articles/Development/recursivequeriesinsqlserver2005/1760/
regards
Kumar
-
create table #Teams (Id int, ParentId int, name varchar(100), Position int)insert into #Teams(Id, ParentId, Name,Position)
select 1, 0, 'Items', 1 union all
select 2, 0, 'Standings', 3 union all
select 3, 0, 'Test', 2 union all
select 4, 2, 'Liverpool' ,2 union all
select 5, 2, 'AC Milan', 1 union all
select 6, 1, 'Itemno1' ,1 ;
with cte(ParentId, Id, name, level, fullpath)
as (
select ParentId, Id, name, 0, cast(1000+Position as varchar(max))
from #Teams
where ParentId=0
union all
select p.ParentId, p.Id, p.name, c.level+1, c.fullpath+'/'+cast(1000+p.Position as varchar(max))
from CTE c
inner join #Teams p on p.ParentId=c.Id)
select replicate('-',level*2)+name
from CTE
order by fullpath
drop table #Teams
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply