我看不出动态SQL的必要性，你有层次数据，你想遍历深度优先：在SQL中，这通常是通过递归查询完成。
若要管理行的顺序，可以跟踪每个节点的路径。
考虑：

with cte as (
    select t.*, 1 lvl, cast(child as nvarchar(max)) path 
    from temp t 
    where parent is null
    union all
    select t.*, c.lvl + 1, c.path + '/' + cast(t.child as nvarchar(max))
    from cte c
    inner join temp t on t.parent = c.child
)
select * from cte order by path

Demo on DB Fiddle：

Pos | Child | Parent | Test | lvl | path   
--: | :---- | :----- | ---: | --: | :------
  1 | A     | *null*   |    1 |   1 | A      
  1 | B     | A      |    2 |   2 | A/B    
  1 | C     | B      |    3 |   3 | A/B/C  
  2 | D     | B      |    4 |   3 | A/B/D  
  2 | E     | A      |    5 |   2 | A/E    
  1 | F     | E      |    6 |   3 | A/E/F  
  2 | G     | E      |    7 |   3 | A/E/G  
  1 | H     | G      |    8 |   4 | A/E/G/H
  2 | I     | G      |    9 |   4 | A/E/G/I
  2 | J     | *null*   |   10 |   1 | J      
  1 | K     | J      |   11 |   2 | J/K    
  2 | L     | J      |   12 |   2 | J/L    
  3 | M     | J      |   13 |   2 | J/M    
  1 | N     | M      |   14 |   3 | J/M/N  
  2 | O     | M      |   15 |   3 | J/M/O  
  3 | P     | *null*   |   16 |   1 | P      
  1 | Q     | P      |   17 |   2 | P/Q    
  1 | R     | Q      |   18 |   3 | P/Q/R  
  2 | S     | P      |   19 |   2 | P/S    
  3 | T     | P      |   20 |   2 | P/T    
  1 | U     | T      |   21 |   3 | P/T/U  
  2 | V     | T      |   22 |   3 | P/T/V  
  3 | W     | T      |   23 |   3 | P/T/W  
  4 | X     | P      |   24 |   2 | P/X    
  4 | Y     | *null*   |   25 |   1 | Y      
  5 | Z     | *null*   |   26 |   1 | Z

如果一个路径可能有100个以上的节点，那么您需要在查询的末尾添加option(maxrecursion 0)，否则您将达到SQL Server默认允许的最大递归级别。

您可以通过搜索递归查询（如articles或更早的答案）上的材料来找到有关您所问问题的材料。
要创建递归查询，您需要创建一个CTE，其中第一个表是锚，就像第一个级别中有列Parent是NULL一样。在同一个CTE中，您不断向该级别添加1。请在Fiddle中找到答案

WITH MyCTE AS (
SELECT *, 1 AS Level
FROM temp
WHERE Parent IS NULL

UNION ALL

SELECT t.Pos, t.Child, t.Parent, t.Test, MyCTE.Level+1 AS Level
FROM temp AS t
INNER JOIN MyCTE
ON t.Parent = MyCTE.Child
WHERE t.Parent IS NOT NULL)
SELECT MyCTE.*, CASE WHEN Offsprings.Offspring IS NULL THEN 1 ELSE Offsprings.Offspring END AS Offspring
FROM MyCTE
LEFT JOIN (
    SELECT Parent, COUNT(Parent) AS Offspring
    FROM temp
    GROUP BY Parent)Offsprings
ON MyCTE.Child = Offsprings.Parent
ORDER BY MyCTE.Child

似乎相同的答案（我想到的..）张贴的其他同行与伟大的演示。然而，以下示例和this post可能有助于简单和更好地理解递归CTE

DDL公司

create table temp 
(
    recid int identity (1,1)
    ,Pos_ID int
    ,Child_Pos nvarchar(50)
    ,Parent_Pos nvarchar(50)
);

insert into temp (Pos_ID, Child_Pos, Parent_Pos)
values
(1, 'Super Boss', NULL),
(2, 'Boss', 'Super Boss'),
(3, 'Sr. Mangaer 1', 'Boss'),
(3, 'Sr. Mangaer 2', 'Boss'),
(3, 'Sr. Mangaer 3', 'Boss'),
(4, 'Mangaer 1', 'Sr. Mangaer 1'),
(4, 'Mangaer 2', 'Sr. Mangaer 1'),
(4, 'Mangaer 3', 'Sr. Mangaer 2'),
(4, 'Mangaer 4', 'Sr. Mangaer 2'),
(4, 'Mangaer 5', 'Sr. Mangaer 3'),
(4, 'Mangaer 6', 'Sr. Mangaer 3'),
(5, 'Emp 01', 'Mangaer 1'),
(5, 'Emp 02', 'Mangaer 1'),
(5, 'Emp 03', 'Mangaer 2'),
(5, 'Emp 04', 'Mangaer 2'),
(5, 'Emp 05', 'Mangaer 3'),
(5, 'Emp 06', 'Mangaer 3'),
(5, 'Emp 07', 'Mangaer 4'),
(5, 'Emp 08', 'Mangaer 4'),
(5, 'Emp 09', 'Mangaer 5'),
(5, 'Emp 10', 'Mangaer 5'),
(5, 'Emp 11', 'Mangaer 6'),
(5, 'Emp 12', 'Mangaer 6')
go

递归CTE示例

with main as (
select Child_Pos, Parent_Pos,Pos_ID, 1 as Reculevel
from temp as t1
--where Parent_Pos is not null 

UNION ALL

select t2.Child_Pos, t2.Parent_Pos, t2.Pos_ID, main.Reculevel + 1
from temp as t2 
join main on t2.Parent_Pos = main.Child_Pos
)

select * from main

在您的示例中遵循递归CTE

with main as (
select Pos, Child, Parent, Test, 1 as RecuLevel
from temp as t1

UNION ALL

select t2.Pos, t2.Child, t2.Parent, t2.Test, RecuLevel + 1
from temp as t2 
join main on t2.Parent = main.Child
)

select * from main
--option (maxrecursion 0) -- be cautious enabling this!