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<>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<>fiddle here

StackExchange Database Administrators Q#172565, answer score: 2