Make Organization

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