我相信你的思路基本上是对的，但是，由于你已经定义了列（例如，Master_Father_ID、Hierarchy和Child_ID），所以你不需要动态SQL。
下面的代码不是递归CTE，而是WHILE循环，以帮助理解发生了什么。转换为递归CTE很容易，但我认为（特别是对许多熟悉其他编程语言的人来说）WHILE循环更熟悉。
下面的逻辑如下
1.找到任何"大师级人物"并将其插入工作表
1.对于在上一步中插入的每个值，查找其直接子项并将其插入到工作表中
1.重复上面的步骤2（例如，第3轮找到子节点的子节点;轮4找到子的子的子）直到不再插入数据
然后，最终报告只需忽略插入的初始Master_Father行（例如，上表中的001、002等）。
下面的SQL也可以在this db<>fiddle中找到-注意，我添加了额外的数据用于演示。
初始数据

CREATE TABLE #exploded_table (
        Father_ID int, 
        Child_ID int, 
        PRIMARY KEY (Father_ID, Child_ID)
        );

INSERT INTO #exploded_table (Father_ID, Child_ID) VALUES
(1,  2),  (1,  3),  (1,  7),  (3,  4),  (3,  5),  (3,  6);

现在来看工作表--我将其命名为#summary_table。Current_ID表示该行的当前Child_ID（例如，在层次结构的最底层）。insert_round是该行被插入的舍入数（循环迭代）。
我还有两个变量：@round表示我们到达了哪一轮/周期，@n表示当前轮中插入的行数。

CREATE TABLE #summary_table (
            Master_Father_ID int, 
            Current_ID int, 
            Hierarchy nvarchar(500), 
            insert_round int
            );

DECLARE @round int = 1;
DECLARE @n int = 0;

• • 第1步：插入父级**

主父亲是那些本身不是子的父亲，例如，Father_Id1不是子，因此它被包括; Father_ID 3是子对象，因此不是主对象。
注意，我使用RIGHT(N'000' + LTRIM(STR(int value)), 3)将任何整数转换为3位数格式的字符串。

INSERT INTO #summary_table (Master_Father_ID, Current_ID, Hierarchy, insert_round)
    SELECT  DISTINCT Father_ID, 
            Father_ID, 
            RIGHT(N'000' + LTRIM(STR(DENSE_RANK() OVER (ORDER BY Father_ID))), 3), 
            @round
    FROM    #exploded_table
    WHERE   Father_ID NOT IN (SELECT Child_ID FROM #exploded_table);

SET @n = @@ROWCOUNT;

• • 第2步：查找上一轮的所有子代，并将其数据插入工作表**

(Also第3步：重复，直到不再发生）
从最后一轮插入（由#summary_table. insert_round标识）中获取数据，标识任何子代并将其数据插入工作表。
继续执行此操作，直到不再插入任何行。

WHILE @n > 0
    BEGIN

    INSERT INTO #summary_table (Master_Father_ID, Current_ID, Hierarchy, insert_round)
        SELECT  DISTINCT
                #summary_table.Master_Father_ID, 
                #exploded_table.Child_ID, 
                #summary_table.Hierarchy + N'.' + RIGHT(N'000' + LTRIM(STR(DENSE_RANK() OVER (PARTITION BY #summary_table.Master_Father_ID, #summary_table.Current_ID ORDER BY #exploded_table.Child_ID))), 3),
                @round + 1
        FROM    #summary_table
                INNER JOIN #exploded_table ON #summary_table.Current_ID = #exploded_table.Father_ID
        WHERE   #summary_table.insert_round = @round;

    SET @n = @@ROWCOUNT;

    SET @round += 1;
    
    END;

最后的报告

SELECT      Master_Father_ID, Hierarchy, Current_ID AS Child_ID
    FROM    #summary_table 
    WHERE   insert_round > 1
    ORDER BY Hierarchy;

数据如下

Master_Father_ID    Hierarchy       Child_ID
1                   001.001         2
1                   001.002         3
1                   001.002.001     4
1                   001.002.002     5
1                   001.002.003     6
1                   001.003         7

注意-这假设你的原始数据设置正确，没有循环/等（例如，如果凯尔里斯是约翰康纳的孩子以及他的父亲......呃对不起，'spolier alert'）。如果你有这些，你需要添加额外的检查-这些检查将取决于你实际上想如何处理这些循环。

您应该能够使用Recursive CTE完成此操作。
我还提供了一个构建SQL Server层次结构数据类型（HIERARCHYID）的示例。
有关详细信息，请参见代码中的注解

-- Test data based on your example
CREATE TABLE #temp( FatherID INT, ChildID INT )
INSERT INTO #temp
VALUES ( 1, 2 ), ( 1, 3 ), ( 1, 7 ), ( 3, 4 ), ( 3, 5 ), ( 3, 6 ),
-- I have added a 3rd level
( 6, 8 ), ( 6, 9 )
-- SELECT * FROM #temp;

-- Recursive CTE to get Children of Children and construct Hierarchy
;WITH Hierarchies( MasterFatherID, FatherID, ChildID, Hierarchy, Hierarchy2 )
AS(
    -- This is the "anchor" part
    SELECT FatherID AS MasterFatherID, FatherID, ChildID,
        FORMAT( DENSE_RANK() OVER( PARTITION BY FatherID ORDER BY ChildID ), '000' ) AS Hierarchy,
        -- This is an example of SQL Server built in Hierarchy data type
        CAST( '/' + FORMAT( DENSE_RANK() OVER( PARTITION BY FatherID ORDER BY ChildID ), '0' ) + '/' AS HIERARCHYID ) AS Hierarchy2
    FROM #temp
    UNION ALL
    SELECT
        -- Top level Parent
        Hierarchies.MasterFatherID,
        -- Current Parent
        t.FatherID,
        -- Current Child
        t.ChildID,
        Hierarchies.Hierarchy + '.' + FORMAT( DENSE_RANK() OVER( PARTITION BY t.FatherID ORDER BY t.ChildID ), '000' ) AS Hierarchy,
        -- This is an example of SQL Server built in Hierarchy data type
        HIERARCHYID::Parse( Hierarchies.Hierarchy2.ToString() + FORMAT( DENSE_RANK() OVER( PARTITION BY t.FatherID ORDER BY t.ChildID ), '0' ) + '/' ) AS Hierarchy2
    FROM Hierarchies
        INNER JOIN #temp AS t ON Hierarchies.ChildID = t.FatherID
)
SELECT MasterFatherID, FatherID, ChildID, Hierarchy, Hierarchy2.ToString() AS Hierarchy2
FROM Hierarchies AS a
-- This will exclude Children (e.g. [3, 4]) from being included as their own chain
WHERE NOT EXISTS( SELECT * FROM Hierarchies AS Children WHERE Children.ChildID = a.MasterFatherID )
ORDER BY MasterFatherID

结果

MasterFatherID FatherID    ChildID     Hierarchy      Hierarchy2
-------------- ----------- ----------- -------------- ----------
1              1           2           001            /1/
1              1           3           002            /2/
1              1           7           003            /3/
1              3           4           002.001        /2/1/
1              3           5           002.002        /2/2/
1              3           6           002.003        /2/3/
1              6           8           002.003.001    /2/3/1/
1              6           9           002.003.002    /2/3/2/