tsql树形数据处理（更新版）

 和前一个版本比有些不同处，如字段变更为长字符形，处理要细微些。示例数据文件在这里，不能直接上传，所以是个压缩包文件。

ALTER FUNCTION ufn_id()
--因为要排序的树形数据表的字段编码为字符型，且最长的长度为20位了，所以函数中的虚拟表相对应的字段要更长些。
	RETURNS @re TABLE (
      id VARCHAR(200) ,		--当前节点编码
      pid VARCHAR(200) ,	--上级节点编码
      level INT ,			--层级区别
      sid VARCHAR(8000) ,	--排序编码区别码
      pre1 VARCHAR(2) ,		--节点标识
      pre2 VARCHAR(2)		--节点标识
    )
AS 
BEGIN
	-- 排序编码取长度时:取当前字段值长度，还是取最大值的长度??? 取最大值来统一补充位数，方便排序.
	DECLARE @len INT
	SET @len = ( SELECT MAX(LEN(code)) FROM dbo.ZlDept )
	DECLARE @l INT 
	SET @l = 0 
	INSERT  @re
        SELECT  code, upcode, @l, RIGHT('0000' + code, @len), '', ''
        FROM    dbo.ZlDept
        WHERE   upcode = ''		
	WHILE @@rowcount > 0 
		BEGIN 
			SET @l = @l + 1 
			INSERT  @re
                SELECT  a.code, a.upcode, @l, b.sid + ',' + RIGHT('0000' + a.code, @len), '', ''
                FROM    dbo.ZlDept a , @re b
                WHERE   b.id = a.UpCode
                        AND b.level = @l - 1 
		END 

	UPDATE  a
	SET     pre2 = CASE WHEN a.level > 1
                    THEN CASE id
                           WHEN ( SELECT RIGHT(MAX(sid), MAX(LEN(id)))
                                  FROM      @re
                                  WHERE     pid = a.pid
                                            AND level = a.level ) 
                           THEN '└'
                           ELSE '├'
                         END
                    ELSE CASE id
                           WHEN ( SELECT RIGHT(MAX(sid), MAX(LEN(id)))
                                  FROM      @re
                                  WHERE     level = a.level ) 
                           THEN '└'
                           ELSE '├'
                         END
					END
	FROM    @re a
	WHERE   a.level > 0 

	UPDATE  a
	SET     pre1 = CASE WHEN pid = ( SELECT RIGHT(MAX(sid), MAX(LEN(id)))
                                 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 b.pre2+REPLICATE('-',4)+a.name 
else REPLICATE(b.pre1+REPLICATE('',(b.level-1)*4),b.[level]-1)+b.pre2+REPLICATE('-',4)+a.name 
end,a.name,a.Code
from zldept a,ufn_id() b 
where a.code=b.id order by b.sid