Make Organization

[frankno1105]

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?

[#2]

Try use PIVOT? Recured CTE is difficult fill multiple columns with one column source.

[#3]

@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. 

[#4 · Accepted solution]

@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

[#5]

This worked perfectly! Thank you