# 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

My output is correct with your given input but you should try with other sample data.

Explain it.Tell me in which sample data it don't work.

In real life how many number of rows will be manipulated at a time ?

``````declare @t table(PositionCode varchar(20),PositionName varchar(20)
,ParentCode varchar(20),Levels int)
insert into @t 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)

;With CTE as
(
select PositionCode as CurCode,PositionName ,levels

,(select top 1  case when t.levels<0 then 'na' else t1.PositionCode end
from @t t1 where levels=case when t.levels>=0 then 0 else t.levels end)[Lvl0Code]

,(select top 1  case when t.levels<1 then 'na' else t1.PositionCode end
from @t t1 where levels=case when t.levels>=1 then 1 else t.levels end)[Lvl1Code]

,(select top 1  case when t.levels<2 then 'na' else
case when t1.Levels=t.Levels  then t.PositionCode else t.ParentCode END
end
from @t t1 where levels=case when t.levels>=2 then 2 else t.levels end)[Lvl2Code]

,(select top 1  case when t.levels<3 then 'na' else
case when t1.Levels=t.Levels  then t.PositionCode else t.ParentCode END
end
from @t t1 where levels=case when t.levels>=3 then 3 else t.levels end)[Lvl3Code]

from @t t
)

select CurCode,[Lvl0Code]
,isnull((select PositionName from @t t where positioncode=[Lvl0Code]),'na')[Lvl0Name]
,[Lvl1Code]
,isnull((select PositionName from @t t where positioncode=[Lvl1Code]),'na')[Lvl1Name]
,[Lvl2Code]
,isnull((select PositionName from @t t where positioncode=[Lvl2Code]),'na')[Lvl2Name]
,[Lvl3Code]
,isnull((select PositionName from @t t where positioncode=[Lvl3Code]),'na')[Lvl3Name]
from cte c
``````