SQL 层级数据查询出树形状态

 

WITH TEST AS
(
SELECT  DEPTID,PARENTDEPT,SORTORDER,1 SPAC,CONVERT(CHAR(200),RTRIM(DEPTID)+CONVERT(CHAR(5),ROW_NUMBER() OVER(ORDER BY  SORTORDER))) SORTID  FROM FSe7en_Org_DeptStruct WHERE DEPTID='dep00000' --ORDER BY SORTORDER
UNION ALL
SELECT  D.DEPTID,D.PARENTDEPT,D.SORTORDER,SPAC+1 as SPAC,CONVERT(CHAR(200),rtrim(SORTID)+RTRIM(D.PARENTDEPT)+CONVERT(CHAR(5),ROW_NUMBER() OVER(ORDER BY  D.SORTORDER)))  SORTID FROM FSe7en_Org_DeptStruct D,TEST M WHERE M.DEPTID=D.ParentDept
)
SELECT   M.DEPTID,PARENTDEPT,SPAC,REPLICATE('  ',SPAC)+M.DEPTID+'-'+DISPLAYNAME DISPLAYNAME FROM TEST M,FSe7en_Org_DeptInfo I
WHERE M.DEPTID=I.DEPTID
order BY SORTID