title: Transform a parent/child table to a fixed column dimentional table
body: <p>I've a relational table (id, parentId, name)</p>
  
  <p>which I'd like to convert to a flattened dimentional table</p>
  
  <p>(id, Level1, Level2, Level3, Level4)</p>
  
  <p>I'm ok fixing the depth at 4 deep.</p>
  
  <p>I've made progress with a recursive CTE and pivot, but the result set isn't right</p>
  
  <p>I get</p>
  
  <pre><code>Id  Name   Level1 Level2
  0   Root   NULL   NULL
  1   NULL   L1     NULL
  </code></pre>
  
  <p>but I need</p>
  
  <pre><code>Id  Name   Level1 Level2
  0   Root   NULL   NULL
  1   Root   L1     NULL
  </code></pre>
  
  <p>here's what I have to date</p>
  
  <pre><code>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
  </code></pre>
  
  <p>what am I doing wrong?</p>