SQL Server 2012

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

6 rows affected

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

CurrCode	Lvl0Code	Lvl0Name	Lvl1Code	Lvl1Name	Lvl2Code	Lvl2Name	Lvl3Code	Lvl3Name	level
AA0001	AA0001	CEO	na	na	na	na	na	na	0
AA0001	AA0001	CEO	AA0002	CFO	na	na	na	na	1
AA0001	AA0001	CEO	AA0002	CFO	AA0003	CIO	na	na	2
AA0001	AA0001	CEO	AA0002	CFO	AA0004	SnrMgr	na	na	2
AA0001	AA0001	CEO	AA0002	CFO	AA0003	CIO	AA0006	ItMgr	3
AA0001	AA0001	CEO	AA0002	CFO	AA0004	SnrMgr	AA0005	JnrMgr	3