Hierarchical structure, new columns, denormalization

Question

Suppose I have table like this:

id  parent_id    name
11     NULL      Company
33      11       Department 1
44      33       Department 2

I would like to transform it into:

id    parent_id    name         Level1      Level2       Level3
11      NULL       Company       NULL        NULL         NULL
22      11         Company   Department 1    NULL         NULL
33      22         Company   Department 1  Department 2   NULL

I am able to create a CTE and come up with Levels column showing a value in hierarchy, but I don't know how to make new columns for departments as presented.

with myCTE as (
      select c.id, c."name", c.parent_id, 1 as Level
      from table1 c
      where c.parent_id IS NULL

      UNION ALL

      Select c1.id, c1."name", c1.parent_id, Level +1
      from table1 c1
      inner join myCTE on c1.parent_id = myCTE.id
      where c1.parent_id IS NOT NULL
      )

select * from myCTE

showing:

         id       parent_id    name         level
   1     11        NULL        Company        1
   2     22        11          Department 2   2
   3     33        22          Department 3   3

Answer 1

An (almost) fully generic approach:

DECLARE @tbl TABLE(id INT,parent_id INT,name VARCHAR(100));
INSERT INTO @tbl VALUES
 (11,NULL,'Company')
,(33,11,'Department 1')
,(44,33,'Department 2a')
,(55,33,'Department 2b')
,(66,44,'SubDep 2a');

--The recursive CTE will build an XML fragment on a row-by-row level --The SELECT will use XML method .nodes() and ROW_NUMBER to generate column names for PIVOT

WITH recCTE AS
(
    SELECT id, parent_id,name,(SELECT name AS [*] FOR XML PATH('')) AS NameConcat 
    FROM @tbl WHERE parent_id IS NULL

    UNION ALL

    SELECT t.id,t.parent_id,t.name,recCTE.NameConcat + '</lvl><lvl>' + (SELECT t.name AS [*] FOR XML PATH(''))
    FROM @tbl AS t
    INNER JOIN recCTE ON recCTE.id=t.parent_id
)
SELECT p.*
FROM
(
    SELECT id
          ,parent_id
          ,name
          ,'Level' + REPLACE(STR(ROW_NUMBER() OVER(PARTITION BY id ORDER BY (SELECT NULL)),2),' ','0') AS HierarchyRank
          ,lvl.value(N'(./text())[1]','nvarchar(max)') AS HierarchyName
    FROM recCTE
    CROSS APPLY (SELECT CAST('<lvl>' + NameConcat + '</lvl>' AS XML) AS PreLevels ) AS Casted
    CROSS APPLY Casted.PreLevels.nodes(N'/lvl') AS A(lvl)
) AS tbl
PIVOT
(
    MAX(HierarchyName) FOR HierarchyRank IN(Level01,Level02,Level03,Level04,Level05,Level06,Level07,Level08,Level09)
) AS p;

The result

+----+-----------+----------------+---------+----------------+---------------+-----------+---------+
| id | parent_id | name           | Level01 | Level02        | Level03       | Level04   | Level05 |
+----+-----------+----------------+---------+----------------+---------------+-----------+---------+
| 11 | NULL      | Company        | Company | NULL           | NULL          | NULL      | NULL    |
+----+-----------+----------------+---------+----------------+---------------+-----------+---------+
| 33 | 11        | Department 1   | Company | Department   1 | NULL          | NULL      | NULL    |
+----+-----------+----------------+---------+----------------+---------------+-----------+---------+
| 44 | 33        | Department 2a  | Company | Department   1 | Department 2a | NULL      | NULL    |
+----+-----------+----------------+---------+----------------+---------------+-----------+---------+
| 55 | 33        | Department 2b  | Company | Department   1 | Department 2b | NULL      | NULL    |
+----+-----------+----------------+---------+----------------+---------------+-----------+---------+
| 66 | 44        | SubDep 2a      | Company | Department   1 | Department 2a | SubDep 2a | NULL    |
+----+-----------+----------------+---------+----------------+---------------+-----------+---------+

If you need more levels, the only need was to add more column names into the PIVOT part...