create table t(ID int, name varchar(10), fatherId varchar(10))
insert into t
select 1, '11 ', '0 ' union all
select 2, '22 ', '1 ' union all
select 3, '33 ', '2 ' union all
select 4, '44 ', '1 ' union all
select 5, '55 ', '3 '
go
declare @temp table(tid int,tlevel int,tsort varchar(1000))
declare @mlevel int
set @mlevel = 0
insert @temp select id,@mlevel,right( '0000 '+str(id,len(id)),4)
from t
where fatherid = 0
while @@rowcount> 0
begin
set @mlevel = @mlevel +1
insert @temp
select a.id,@mlevel,b.tsort+right( '0000 '+str(a.id,len(a.id)),4)
from t a,@temp b
where a.fatherid = b.tid and b.tlevel = @mlevel -1
end
select space(b.tlevel*2) + '+ ' + a.name
from t a join @temp b on a.id = b.tid
order by b.tsort
go
insert into t
select 1, '11 ', '0 ' union all
select 2, '22 ', '1 ' union all
select 3, '33 ', '2 ' union all
select 4, '44 ', '1 ' union all
select 5, '55 ', '3 '
go
declare @temp table(tid int,tlevel int,tsort varchar(1000))
declare @mlevel int
set @mlevel = 0
insert @temp select id,@mlevel,right( '0000 '+str(id,len(id)),4)
from t
where fatherid = 0
while @@rowcount> 0
begin
set @mlevel = @mlevel +1
insert @temp
select a.id,@mlevel,b.tsort+right( '0000 '+str(a.id,len(a.id)),4)
from t a,@temp b
where a.fatherid = b.tid and b.tlevel = @mlevel -1
end
select space(b.tlevel*2) + '+ ' + a.name
from t a join @temp b on a.id = b.tid
order by b.tsort
go