Forum Discussion
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:
ID | Name | MOTHER_ID | Level |
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 |
I want to "flatten it out" so it has this structure:
Level 1 ID | Level 1 Name | Level 2 ID | Level 2 Name | Level 3 ID | Level 3 Name | Level 4 ID | Level 4 Name | Level 5 ID | Level 5 Name | Level 6 ID | Level 6 Name |
And the result should be like this:
Level 1 ID | Level 1 Name | Level 2 ID | Level 2 Name | Level 3 ID | Level 3 Name | Level 4 ID | Level 4 Name | Level 5 ID | Level 5 Name | Level 6 ID | Level 6 Name |
10020027 | Level 1 name | 10020027 | Level 1 name | 10020027 | Level 1 name | 10020027 | Level 1 name | 10020027 | Level 1 name | 10020027 | Level 1 name |
10020027 | Level 1 name | 10020028 | Level 2 name | 10020028 | Level 2 name | 10020028 | Level 2 name | 10020028 | Level 2 name | 10020028 | Level 2 name |
10020027 | Level 1 name | 10020028 | Level 2 name | 10020032 | Level 3 Name | 10020032 | Level 3 Name | 10020032 | Level 3 Name | 10020032 | Level 3 Name |
10020027 | Level 1 name | 10020028 | Level 2 name | 10020032 | Level 3 Name | 10020035 | Level 4 Name | 10020035 | Level 4 Name | 10020035 | Level 4 Name |
10020027 | Level 1 name | 10020028 | Level 2 name | 10020032 | Level 3 Name | 10020035 | Level 4 Name | 10020166 | Level 5 Name | 10020166 | Level 5 Name |
10020027 | Level 1 name | 10020028 | Level 2 name | 10020032 | Level 3 Name | 10020035 | Level 4 Name | 10020166 | Level 5 Name | 10020038 | Level 6 Name |
10020027 | Level 1 name | 10020028 | Level 2 name | 10020083 | Level 3 Name | 10020083 | Level 3 Name | 10020083 | Level 3 Name | 10020083 | Level 3 Name |
10020027 | Level 1 name | 10020028 | Level 2 name | 10020083 | Level 3 Name | 10020188 | Level 4 Name | 10020188 | Level 4 Name | 10020188 | 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 ID | Level 1 Name | Level 2 ID | Level 2 Name | Level 3 ID | Level 3 Name | Level 4 ID | Level 4 Name | Level 5 ID | Level 5 Name | Level 6 ID | Level 6 Name |
10020027 | Level 1 name | 10020028 | Level 2 name | 10020028 | Level 2 name | 10020028 | Level 2 name | 10020028 | Level 2 name | 10020028 | Level 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 ID | Level 1 Name | Level 2 ID | Level 2 Name | Level 3 ID | Level 3 Name | Level 4 ID | Level 4 Name | Level 5 ID | Level 5 Name | Level 6 ID | Level 6 Name |
10020027 | Level 1 name | 10020028 | Level 2 name | 10020032 | Level 3 Name | 10020032 | Level 3 Name | 10020032 | Level 3 Name | 10020032 | Level 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?
My implementation is a bit complicated:
- Use recursive CTE to flat all mother nodes, it constructs a result set named C1.
- 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.
- Then you can use any method you like to query out the final result.
- 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
- rodgerkongIron ContributorTry use PIVOT? Recured CTE is difficult fill multiple columns with one column source.
- frankno1105Copper 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.
- rodgerkongIron Contributor
My implementation is a bit complicated:
- Use recursive CTE to flat all mother nodes, it constructs a result set named C1.
- 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.
- Then you can use any method you like to query out the final result.
- 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