Transform a parent/child table to a fixed column dimentional table

Question

I've a relational table (id, parentId, name)

which I'd like to convert to a flattened dimentional table

(id, Level1, Level2, Level3, Level4)

I'm ok fixing the depth at 4 deep.

I've made progress with a recursive CTE and pivot, but the result set isn't right

I get

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

but I need

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

here's what I have to date

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

what am I doing wrong?

Answer 1

Overcomplicating the solution? If it's fixed at four deep then it can be done with some simple joins...

SELECT
    L1.id as ID
    L1.Name as Level1
    L2.Name as Level2
    L3.Name as Level3
    L4.Name as Level4
FROM
    RelTable as L1

        INNER JOIN
    RelTable as L2
        ON L1.id = L2.ParentID

        INNER JOIN
    RelTable as L3
        ON L2.id = L3.ParentID

        INNER JOIN
    RelTable as L4
        ON L3.id = L4.ParentID

As an exercise in using CTEs its useless, but it does what you need.