How to flatten and pivot a normalised hierarchy table
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