Yo conozco 2 formas, esta es una, digame si le sirve:
set nocount on
--build test tables
declare @treeTable table (id int, text varchar(10), father int)
insert into @treeTable
select 1,'txt1',0
union all
select 2,'txt1',0
union all
select 3,'txt1',1
union all
select 4,'txt1',2
union all
select 5,'txt1',3
union all
select 6,'txt1',2
----------------------------------
declare @output table (id int, text varchar(10), father int, sortOut varchar(1000), level int)
--get all top level nodes
insert into @output
--the id will be used (padded to 10 chars for sorting)
select id, text, father, str(id,10), 1 --the level will be used to keep us from seeing the same rows
--multiple times
from @treeTable
where father = 0
declare @level int
set @level = 1
while (1=1)
begin
insert into @output --add parent sort and new id --increment level
select child.id, child.text, child.father, parent.sortOut + str(child.id,10),@level + 1
from @treeTable as child
join @output as parent
on child.father = parent.id
where parent.level = @level
if @@rowcount = 0 --no rows inserted means we are done
break
set @level = @level + 1
end
select id, text, father
from @output
order by sortOut
