2

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.

1

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

|improve this answer|||||
0

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
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.