tsql 树形数据的处理

--创建测试数据表.
create table tb(id int,name varchar(10),parentid int) 
  insert tb select 1,'aaaa'   ,0 
  union all select 2,'bbbb'   ,0 
  union all select 3,'cccc'   ,0 
  union all select 4,'aaaa-1'   ,1 
  union all select 5,'aaaa-2'   ,1 
  union all select 6,'bbbb-1'   ,2 
  union all select 7,'cccc-1'   ,3 
  union all select 8,'cccc-2'   ,3 
  union all select 9,'aaaa-1-1',4 
  union all select 10,'cccc-2-1',8 
  union all select 11,'cccc-2-2',8 
  union all select 12,'aaaa-1-2',4 
  go 
  
--创建表值函数(对原函数做了修改，增加了前缀) 
alter function dbo. f_id() 
  returns @re table(id int,parentid int,level int,sid varchar(8000),pre1 varchar(2),pre2 varchar(2)) 
  as 
begin 
    declare @l int 
    set @l=0 
    insert @re select id,parentid,@l,right(10000+id,4),'','' 
    from tb where parentid=0 
    while @@rowcount>0 
    begin 
      set @l=@l+1 
      insert @re select a.id,a.parentid,@l,b.sid+','+right(10000+a.id,4),'',''  
      from tb a,@re b 
      where a.parentid=b.id and b.level=@l-1 
    end 
update a set pre2= 
case 
when a.level>1 then case id when (select convert(int,right(max(sid),4)) from @re where parentid=a.parentid and level=a.level) then '└' else '├' end 
else case id when (select convert(int,right(max(sid),4)) from @re where level=a.level) then '└' else '├' end 
END from @re a where a.level>0 
update a set pre1= 
CASE
when parentid=(select convert(int,right(max(sid),4)) from @re where level=a.level-1) then '' else '│' 
END from @re a where a.level>1 
    RETURN  
END 

--返回结果集.
select [带标记的name]=case when b.[level]=0 then a.name 
when b.[level]=1 then pre2+replicate('-',4)+a.name 
else b.pre1+replicate('',(b.level-1)*4)+b.pre2+replicate('-',4)+a.name 
end,a.name,a.id 
from tb a,f_id() b where a.id=b.id order by b.sid  

/*结果:
带缩进的name             name       id
--------------------               ---------- -----------
aaaa                            aaaa       1
├----aaaa-1                  aaaa-1     4
│├----aaaa-1-1             aaaa-1-1   9
│└----aaaa-1-2             aaaa-1-2   12
├----aaaa-2                  aaaa-2     5
bbbb                            bbbb       2
├----bbbb-1                  bbbb-1     6
cccc                            cccc       3
├----cccc-1                  cccc-1     7
└----cccc-2                  cccc-2     8
├----cccc-2-1               cccc-2-1   10
└----cccc-2-2               cccc-2-2   11
*/

 

引用资源:http://topic.csdn.net/t/20040904/18/3340610.html
http://www.cnblogs.com/drc/archive/2005/11/04/269062.html