clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2042750 fiddles created (16515 in the last week).

create table tbl (PositionCode nvarchar(10), PositionName nvarchar(10), ParentCode nvarchar(10), Level int); insert into tbl values ('AA0001', 'CEO', null, 0), ('AA0002', 'CFO', 'AA0001', 1), ('AA0003', 'CIO', 'AA0002', 2), ('AA0004', 'SnrMgr', 'AA0002', 2), ('AA0005', 'JnrMgr', 'AA0004', 3), ('AA0006', 'ItMgr', 'AA0003', 3);
6 rows affected
 hidden batch(es)


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
 hidden batch(es)