将父表/子表转换为固定列维度表

我有一个关系表(id,parentid,name)

我想把它换成一个扁平的尺寸表

(ID、1级、2级、3级、4级)

我可以把深度定在4度。

我已经在递归CTE和PIVOT方面取得了进展,但是结果集不正确。

我得到

Id  Name   Level1 Level2
0   Root   NULL   NULL
1   NULL   L1     NULL

但我需要

Id  Name   Level1 Level2
0   Root   NULL   NULL
1   Root   L1     NULL

这是我要约会的

with rcte as
(
      select h.id
      ,h.parent_id
      ,h.name
      ,1 as HierarchyLevel 
  FROM RelTable h
  where id = 1
  union all
  select h2.id
       , h2.parent_id 
      , h2.name
      , r.HierarchyLevel + 1 AS HierarchyLevel 
  FROM RelTable h2
  inner join rcte r on h2.parent_id = r.id
 )
select id, parent_id, [1] as L1,[2] as L2,[3] as L3, [4] as L4
from (
select id,parent_id,name,HierarchyLevel from rcte
) as src
pivot  ( max(name)  for HierarchyLevel   in ([1],[2],[3],[4]) ) as pvt

我做错什么了?