How to flatten and pivot a normalised hierarchy table I have an OrgChart table that looks something like this:


PositionCode   PositionName   ParentCode  Level
AA0001         CEO                        0
AA0002         CFO            AA0001      1
AA0003         CIO            AA0002      2
AA0004         SnrMgr         AA0002      2
AA0005         JnrMgr         AA0004      3
AA0006         ItMgr          AA0003      3


etc
So, the ItMgr reports to the CIO, who reports to the CFO, who reports to the CEO.

I need to convert this to a flattened structure like this (for a BI solution): (CurCode is the position we're focusing on for that row, followed by the complete org hierarchy above that position).


CurCode  Lvl0Code Lvl0Name Lvl1Code Lvl1Name Lvl2Code Lvl2Name Lvl3Code Lvl3Name
AA0001   AA0001   CEO      na       na       na       na       na       na
AA0002   AA0001   CEO      AA0002   CFO      na       na       na       na
AA0003   AA0001   CEO      AA0002   CFO      AA0003   CIO      na       na
AA0004   AA0001   CEO      AA0002   CFO      AA0004   SnrMgr   na       na
AA0005   AA0001   CEO      AA0002   CFO      AA0004   SnrMgr   AA0005   JnrMgr
AA0006   AA0001   CEO      AA0002   CFO      AA0003   CIO      AA0006   ItMgr


How can I achieve that in T-SQL?
The table goes 5 levels deep, so I don't need an undefined number of columns. 
I was looking at a PIVOT, but couldn't see how to make it work properly.
Any help will be very appreciated.
As far as you're using a hierarchical structure you can apply a recursive solution. But for a large number of rows you must deal with MAXRECURSION, have a look at MS Docs about it.

Quoted from docs:


  An incorrectly composed recursive CTE may cause an infinite loop. For
  example, if the recursive member query definition returns the same
  values for both the parent and child columns, an infinite loop is
  created. To prevent an infinite loop, you can limit the number of
  recursion levels allowed for a particular statement by using the
  MAXRECURSION hint and a value between 0 and 32,767 in the OPTION
  clause of the INSERT, UPDATE, DELETE, or SELECT statement. This lets
  you control the execution of the statement until you resolve the code
  problem that is creating the loop. The server-wide default is 100.
  When 0 is specified, no limit is applied. Only one MAXRECURSION value
  can be specified per statement.



WITH tree AS
(
    SELECT 
        PositionCode as CurrCode,
        PositionCode,
        PositionCode as Lvl0Code,
        PositionName as Lvl0Name,
        cast('na' as nvarchar(10)) as Lvl1Code,
        cast('na' as nvarchar(10)) as Lvl1Name,
        cast('na' as nvarchar(10)) as Lvl2Code,
        cast('na' as nvarchar(10)) as Lvl2Name,
        cast('na' as nvarchar(10)) as Lvl3Code,
        cast('na' as nvarchar(10)) as Lvl3Name,
        ParentCode,
        Level
    FROM 
        tbl
    WHERE
        Level = 0
    UNION ALL
    SELECT
        tree.CurrCode,
        tbl.PositionCode,
        tree.Lvl0Code,
        tree.Lvl0Name,
        case when tbl.Level = 1 then tbl.PositionCode else tree.Lvl1Code end as Lvl1Code,
        case when tbl.Level = 1 then tbl.PositionName else tree.Lvl1Name end as Lvl1Name,
        case when tbl.Level = 2 then tbl.PositionCode else tree.Lvl2Code end as Lvl2Code,
        case when tbl.Level = 2 then tbl.PositionName else tree.Lvl2Name end as Lvl2Name,
        case when tbl.Level = 3 then tbl.PositionCode else tree.Lvl3Code end as Lvl3Code,
        case when tbl.Level = 3 then tbl.PositionName else tree.Lvl3Name end as Lvl3Name,
        tbl.ParentCode,
        tbl.Level
    FROM 
        tbl
    JOIN
        tree
        ON tree.PositionCode = tbl.ParentCode
)
SELECT
    CurrCode,
    Lvl0Code,
    Lvl0Name,
    Lvl1Code,
    Lvl1Name,
    Lvl2Code,
    Lvl2Name,
    Lvl3Code,
    Lvl3Name
FROM 
       tree
OPTION (MAXRECURSION 0);



CurrCode | Lvl0Code | Lvl0Name | Lvl1Code | Lvl1Name | Lvl2Code | Lvl2Name | Lvl3Code | Lvl3Name
:------- | :------- | :------- | :------- | :------- | :------- | :------- | :------- | :-------
AA0001   | AA0001   | CEO      | na       | na       | na       | na       | na       | na      
AA0001   | AA0001   | CEO      | AA0002   | CFO      | na       | na       | na       | na      
AA0001   | AA0001   | CEO      | AA0002   | CFO      | AA0003   | CIO      | na       | na      
AA0001   | AA0001   | CEO      | AA0002   | CFO      | AA0004   | SnrMgr   | na       | na      
AA0001   | AA0001   | CEO      | AA0002   | CFO      | AA0004   | SnrMgr   | AA0005   | JnrMgr  
AA0001   | AA0001   | CEO      | AA0002   | CFO      | AA0003   | CIO      | AA0006   | ItMgr   


db&lt;>fiddle here

If you don't like or don't want or simply you cannot use a recursive solution, you can use a series of nested CTE's (one for each level), and finally combine the results using a UNION operation:


WITH l0 AS
(
    SELECT PositionCode as CurrCode, PositionCode as Lvl0Code, PositionName as Lvl0Name,
           'na' as Lvl1Code,
           'na' as Lvl1Name,
           'na' as Lvl2Code,
           'na' as Lvl2Name,
           'na' as Lvl3Code,
           'na' as Lvl3Name,
           level
    FROM   tbl 
    WHERE  level = 0
), 
l1 AS
(
    SELECT l0.CurrCode, l0.Lvl0Code, l0.Lvl0Name,
           t1.PositionCode Lvl1Code, 
           t1.PositionName Lvl1Name,
           'na' as Lvl2Code,
           'na' as Lvl2Name,
           'na' as Lvl3Code,
           'na' as Lvl3Name,
           t1.level
    FROM   tbl t1
    JOIN   l0
           ON t1.ParentCode = l0.Lvl0Code
    WHERE  t1.level = 1 
),
l2 AS
(
    SELECT l1.CurrCode, l1.Lvl0Code, l1.Lvl0Name,
           l1.Lvl1Code, 
           l1.Lvl1Name,
           t2.PositionCode Lvl2Code, 
           t2.PositionName Lvl2Name,
           'na' as Lvl3Code,
           'na' as Lvl3Name,
           t2.level
    FROM   tbl t2
    JOIN   l1
           ON t2.ParentCode = l1.Lvl1Code
    WHERE  t2.level = 2 
),
l3 AS
(
    SELECT l2.CurrCode, l2.Lvl0Code, l2.Lvl0Name,
           l2.Lvl1Code, 
           l2.Lvl1Name,
           l2.Lvl2Code, 
           l2.Lvl2Name,
           t3.PositionCode Lvl3Code, 
           t3.PositionName Lvl3Name,
           t3.level
    FROM   tbl t3
    JOIN   l2
           ON t3.ParentCode = l2.Lvl2Code
    WHERE  t3.level = 3
)
SELECT * FROM l0 
UNION
SELECT * FROM l1
UNION
SELECT * FROM l2
UNION
SELECT * FROM l3
ORDER BY CurrCode, level;


db&lt;>fiddle here