展平分层sql输出

一种选择是使用标准的递归cte构建具有路径的层次结构,然后使用少量XML解析路径

这假设您知道最大级别

实例

Declare @YourTable Table ([ManagerID] varchar(50),[EmployeeID] varchar(50))
Insert Into @YourTable Values 
 (null ,'S-1')
,('S-1','S-11')
,('S-1','S-12')
,('S-1','S-13')
,('S-1','S-14')
,('S-1','S-15')
,('S-11','S-111')
,('S-11','S-112')

;with cteP as (
      Select EmployeeID
            ,ManagerID 
            ,PathID = cast(EmployeeID as varchar(max))
      From   @YourTable
      Where  ManagerID is Null
      Union  All
      Select EmployeeID  = r.EmployeeID
            ,ManagerID  = r.ManagerID 
            ,PathID = cast(p.PathID+','+cast(r.EmployeeID as varchar(25)) as varchar(max))
      From   @YourTable r
      Join   cteP p on r.ManagerID  = p.EmployeeID)
Select [EmpID] = EmployeeID
      ,B.*
 From  cteP A
 Cross Apply (
                Select Level1 = xDim.value('/x[1]','varchar(max)')
                      ,Level2 = xDim.value('/x[2]','varchar(max)')
                      ,Level3 = xDim.value('/x[3]','varchar(max)')
                      ,Level4 = xDim.value('/x[4]','varchar(max)')
                      ,Level5 = xDim.value('/x[5]','varchar(max)')
                From  (Select Cast('<x>' + replace(PathID,',','</x><x>')+'</x>' as xml) as xDim) as X 
             ) B

  Order By PathID

退货

EmpID   Level1  Level2  Level3  Level4  Level5
S-1     S-1     NULL    NULL    NULL    NULL
S-11    S-1     S-11    NULL    NULL    NULL
S-111   S-1     S-11    S-111   NULL    NULL
S-112   S-1     S-11    S-112   NULL    NULL
S-12    S-1     S-12    NULL    NULL    NULL
S-13    S-1     S-13    NULL    NULL    NULL
S-14    S-1     S-14    NULL    NULL    NULL
S-15    S-1     S-15    NULL    NULL    NULL