Forum Discussion

frankno1105's avatar
frankno1105
Copper Contributor
Sep 09, 2024

Make Organization

I have a table with organizational structure. It has an ID column called ID. The table also has a column called MOTHER_ID. This refers to the level (MOR) above in the organizational structure. It also has a row that says which level in the organization the row belongs to. We can have 6 levels.
Table looks like this:

IDNameMOTHER_IDLevel
10020027Level 1 nameNULL1
10020028Level 2 name100200272
10020032Level 3 Name100200283
10020035Level 4 Name100200324
10020166Level 5 Name100200355
10020038Level 6 Name100201666
10020083Level 3 Name100200283
10020188Level 4 Name100200834


I want to "flatten it out" so it has this structure:

Level 1 IDLevel 1 NameLevel 2 IDLevel 2 NameLevel 3 IDLevel 3 NameLevel 4 IDLevel 4 NameLevel 5 IDLevel 5 NameLevel 6 IDLevel 6 Name


And the result should be like this: 

Level 1 IDLevel 1 NameLevel 2 IDLevel 2 NameLevel 3 IDLevel 3 NameLevel 4 IDLevel 4 NameLevel 5 IDLevel 5 NameLevel 6 IDLevel 6 Name
10020027Level 1 name10020027Level 1 name10020027Level 1 name10020027Level 1 name10020027Level 1 name10020027Level 1 name
10020027Level 1 name10020028Level 2 name10020028Level 2 name10020028Level 2 name10020028Level 2 name10020028Level 2 name
10020027Level 1 name10020028Level 2 name10020032Level 3 Name10020032Level 3 Name10020032Level 3 Name10020032Level 3 Name
10020027Level 1 name10020028Level 2 name10020032Level 3 Name10020035Level 4 Name10020035Level 4 Name10020035Level 4 Name
10020027Level 1 name10020028Level 2 name10020032Level 3 Name10020035Level 4 Name10020166Level 5 Name10020166Level 5 Name
10020027Level 1 name10020028Level 2 name10020032Level 3 Name10020035Level 4 Name10020166Level 5 Name10020038Level 6 Name
10020027Level 1 name10020028Level 2 name10020083Level 3 Name10020083Level 3 Name10020083Level 3 Name10020083Level 3 Name
10020027Level 1 name10020028Level 2 name10020083Level 3 Name10020188Level 4 Name10020188Level 4 Name10020188

Level 4 Name

 

TThe rows that are on level 6 should have a different id in each column. But rows that is in higher levels are going to mirror the value on the last level into all the missing levels. 

Example

Level 1 row should mirror Level1 values into all columns. 
Level 2 rows will look like this (Level 1 on top, then level 2 on the rest):

Level 1 IDLevel 1 NameLevel 2 IDLevel 2 NameLevel 3 IDLevel 3 NameLevel 4 IDLevel 4 NameLevel 5 IDLevel 5 NameLevel 6 IDLevel 6 Name
10020027Level 1 name10020028Level 2 name10020028Level 2 name10020028Level 2 name10020028Level 2 name10020028Level 2 name


Level 3 rows will look like this (Level 1 on top, then level 2 and then level 3 on the rest):

Level 1 IDLevel 1 NameLevel 2 IDLevel 2 NameLevel 3 IDLevel 3 NameLevel 4 IDLevel 4 NameLevel 5 IDLevel 5 NameLevel 6 IDLevel 6 Name
10020027Level 1 name10020028Level 2 name10020032Level 3 Name10020032Level 3 Name10020032Level 3 Name10020032Level 3 Name


I tried a code like this: 

WITH OrgCTE AS (
    SELECT 
        ID AS Level1_ID,
        Name AS Level1_Name,
        ID AS Level2_ID,
        Name AS Level2_Name,
        ID AS Level3_ID,
        Name AS Level3_Name,
        ID AS Level4_ID,
        Name AS Level4_Name,
        ID AS Level5_ID,
        Name AS Level5_Name,
        ID AS Level6_ID,
        Name AS Level6_Name,
        ID,
        MOTHER_ID,
        Level
    FROM Tabell1
    WHERE Level = 1

    UNION ALL

    SELECT 
        c.Level1_ID,
        c.Level1_Name,
        CASE WHEN p.Level = 2 THEN p.ID ELSE c.Level2_ID END,
        CASE WHEN p.Level = 2 THEN p.Name ELSE c.Level2_Name END,
        CASE WHEN p.Level = 3 THEN p.ID ELSE c.Level3_ID END,
        CASE WHEN p.Level = 3 THEN p.Name ELSE c.Level3_Name END,
        CASE WHEN p.Level = 4 THEN p.ID ELSE c.Level4_ID END,
        CASE WHEN p.Level = 4 THEN p.Name ELSE c.Level4_Name END,
        CASE WHEN p.Level = 5 THEN p.ID ELSE c.Level5_ID END,
        CASE WHEN p.Level = 5 THEN p.Name ELSE c.Level5_Name END,
        CASE WHEN p.Level = 6 THEN p.ID ELSE c.Level6_ID END,
        CASE WHEN p.Level = 6 THEN p.Name ELSE c.Level6_Name END,
        p.ID,
        p.MOTHER_ID,
        p.Level
    FROM Tabell1 p
    INNER JOIN OrgCTE c ON p.MOTHER_ID = c.ID
)
SELECT 
    Level1_ID,
    Level1_Name,
    Level2_ID,
    Level2_Name,
    Level3_ID,
    Level3_Name,
    Level4_ID,
    Level4_Name,
    Level5_ID,
    Level5_Name,
    Level6_ID,
    Level6_Name
FROM OrgCTE
ORDER BY Level1_ID, Level2_ID, Level3_ID, Level4_ID, Level5_ID, Level6_ID;

But this did not mirror the lowest ID for the row. It just put Level1 ID for the missing levels.
Like this:

 


Suggestions?

  • rodgerkong's avatar
    rodgerkong
    Sep 10, 2024

    frankno1105 

    My implementation is a bit complicated:

    1. Use recursive CTE to flat all mother nodes, it constructs a result set named C1.
    2. Use recursive CTE again to flat all child nodes, build a result set named C2. Now it has (node count) * (total level) = 8 * 6= 48 rows, all flated rows are there.
    3. Then you can use any method you like to query out the final result.
    4. I used PIVOT twice to get ID set and Name set, then JOIN them, That's it.

    Here is code with sample data

     

     

    --Build test table
    CREATE TABLE #Organize
    (
    	ID varchar(20),
    	Name varchar(20),
    	Mother_ID varchar(20),
    	Level int
    )
    GO
    
    --Fill test Data, same as the sample
    INSERT INTO #Organize
    VALUES
    ('10020027', 'Level 1 name', NULL, 1)
    ,('10020028', 'Level 2 name', '10020027', 2)
    ,('10020032', 'Level 3 name', '10020028', 3)
    ,('10020035', 'Level 4 name', '10020032', 4)
    ,('10020166', 'Level 5 name', '10020035', 5)
    ,('10020038', 'Level 6 name', '10020166', 6)
    ,('10020083', 'Level 3 name', '10020028', 3)
    ,('10020188', 'Level 4 name', '10020083', 4)
    GO
    
    WITH C1(ID, Name, Mother_ID, Level, Last_Child_ID)  --First CTE, Flat mother nodes
    AS
    (
    	SELECT ID, Name, Mother_ID, Level, ID AS Last_Child_ID FROM
    		#Organize
    	UNION ALL
    	SELECT O.ID, O.Name, O.Mother_ID, O.Level, C1.Last_Child_ID
    	FROM
    		#Organize O INNER JOIN C1
    		ON 
    			C1.Mother_ID = O.ID 
    )
    ,
    C2(ID, Name, Mother_ID, Level, Last_Child_ID) --2nd CTE, Flat child nodes
    AS
    (
    	SELECT ID, Name, Mother_ID, Level, Last_Child_ID FROM
    		C1
    	UNION ALL
    	SELECT C2.ID, C2.Name, C2.ID AS Mother_ID, C2.Level+1 AS Level, C2.Last_Child_ID 
    	FROM
    		C2 WHERE C2.ID = C2.Last_Child_ID AND C2.Level+1 <=6
    )
    SELECT P.Last_Child_ID AS [Org_ID]   --Get result
    ,P.[1] AS [Level 1 ID], N.[1] AS [Level 1 Name]
    ,P.[2] AS [Level 2 ID], N.[2] AS [Level 2 Name]
    ,P.[3] AS [Level 3 ID], N.[3] AS [Level 3 Name]
    ,P.[4] AS [Level 4 ID], N.[4] AS [Level 4 Name]
    ,P.[5] AS [Level 5 ID], N.[5] AS [Level 5 Name]
    ,P.[6] AS [Level 6 ID], N.[6] AS [Level 6 Name]
    FROM 
    (
    	SELECT Last_Child_ID, Level, ID FROM C2
    ) AS SR
    PIVOT   --Get IDs
    (
    	MAX(ID) FOR Level IN ([1],[2],[3],[4],[5],[6])
    )AS P
    INNER JOIN 
    (SELECT N.Last_Child_ID
    ,[1]
    ,[2]
    ,[3]
    ,[4]
    ,[5]
    ,[6]
    FROM 
    (
    	SELECT Last_Child_ID, Level, Name FROM C2
    ) AS SR
    PIVOT   --Get Names
    (
    	MAX(Name) FOR Level IN ([1],[2],[3],[4],[5],[6])
    )AS N)N
    ON P.Last_Child_ID = N.Last_Child_ID
    ORDER BY Org_ID
    
    --Drop test table
    DROP TABLE #Organize

     

    Result is here

  • rodgerkong's avatar
    rodgerkong
    Iron Contributor
    Try use PIVOT? Recured CTE is difficult fill multiple columns with one column source.
    • frankno1105's avatar
      frankno1105
      Copper Contributor

      rodgerkong Want PIVOT only "move" the columns? I also want to make it fill the levels both above and below the level that are existing. 

      • rodgerkong's avatar
        rodgerkong
        Iron Contributor

        frankno1105 

        My implementation is a bit complicated:

        1. Use recursive CTE to flat all mother nodes, it constructs a result set named C1.
        2. Use recursive CTE again to flat all child nodes, build a result set named C2. Now it has (node count) * (total level) = 8 * 6= 48 rows, all flated rows are there.
        3. Then you can use any method you like to query out the final result.
        4. I used PIVOT twice to get ID set and Name set, then JOIN them, That's it.

        Here is code with sample data

         

         

        --Build test table
        CREATE TABLE #Organize
        (
        	ID varchar(20),
        	Name varchar(20),
        	Mother_ID varchar(20),
        	Level int
        )
        GO
        
        --Fill test Data, same as the sample
        INSERT INTO #Organize
        VALUES
        ('10020027', 'Level 1 name', NULL, 1)
        ,('10020028', 'Level 2 name', '10020027', 2)
        ,('10020032', 'Level 3 name', '10020028', 3)
        ,('10020035', 'Level 4 name', '10020032', 4)
        ,('10020166', 'Level 5 name', '10020035', 5)
        ,('10020038', 'Level 6 name', '10020166', 6)
        ,('10020083', 'Level 3 name', '10020028', 3)
        ,('10020188', 'Level 4 name', '10020083', 4)
        GO
        
        WITH C1(ID, Name, Mother_ID, Level, Last_Child_ID)  --First CTE, Flat mother nodes
        AS
        (
        	SELECT ID, Name, Mother_ID, Level, ID AS Last_Child_ID FROM
        		#Organize
        	UNION ALL
        	SELECT O.ID, O.Name, O.Mother_ID, O.Level, C1.Last_Child_ID
        	FROM
        		#Organize O INNER JOIN C1
        		ON 
        			C1.Mother_ID = O.ID 
        )
        ,
        C2(ID, Name, Mother_ID, Level, Last_Child_ID) --2nd CTE, Flat child nodes
        AS
        (
        	SELECT ID, Name, Mother_ID, Level, Last_Child_ID FROM
        		C1
        	UNION ALL
        	SELECT C2.ID, C2.Name, C2.ID AS Mother_ID, C2.Level+1 AS Level, C2.Last_Child_ID 
        	FROM
        		C2 WHERE C2.ID = C2.Last_Child_ID AND C2.Level+1 <=6
        )
        SELECT P.Last_Child_ID AS [Org_ID]   --Get result
        ,P.[1] AS [Level 1 ID], N.[1] AS [Level 1 Name]
        ,P.[2] AS [Level 2 ID], N.[2] AS [Level 2 Name]
        ,P.[3] AS [Level 3 ID], N.[3] AS [Level 3 Name]
        ,P.[4] AS [Level 4 ID], N.[4] AS [Level 4 Name]
        ,P.[5] AS [Level 5 ID], N.[5] AS [Level 5 Name]
        ,P.[6] AS [Level 6 ID], N.[6] AS [Level 6 Name]
        FROM 
        (
        	SELECT Last_Child_ID, Level, ID FROM C2
        ) AS SR
        PIVOT   --Get IDs
        (
        	MAX(ID) FOR Level IN ([1],[2],[3],[4],[5],[6])
        )AS P
        INNER JOIN 
        (SELECT N.Last_Child_ID
        ,[1]
        ,[2]
        ,[3]
        ,[4]
        ,[5]
        ,[6]
        FROM 
        (
        	SELECT Last_Child_ID, Level, Name FROM C2
        ) AS SR
        PIVOT   --Get Names
        (
        	MAX(Name) FOR Level IN ([1],[2],[3],[4],[5],[6])
        )AS N)N
        ON P.Last_Child_ID = N.Last_Child_ID
        ORDER BY Org_ID
        
        --Drop test table
        DROP TABLE #Organize

         

        Result is here

Resources