SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

-- Original table (with additional data)

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),		(21,  22),		(41,  42),		(81, 82),	(91,  92),
(1,  3),		(21,  23),		(41,  43),      (82, 83),
(1,  7),		(21,  27),		(41,  47),		(83, 84),
(3,  4),		(23,  24),		(43,  44),      (84, 85),
(3,  5),		(23,  25),		(43,  45),
(3,  6),		(23,  26),		(43,  46),
								(45,  47),
								(45,  48),
								(46,  49),
								(46,  50),
								(50,  51);
28 rows affected

-- Working table (also used for final data)

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

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

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;

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;
33 rows affected

-- Final result

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

21
002.001
22

21
002.002
23

21
002.002.001
24

21
002.002.002
25

21
002.002.003
26

21
002.003
27

41
003.001
42

41
003.002
43

41
003.002.001
44

41
003.002.002
45

41
003.002.002.001
47

41
003.002.002.002
48

41
003.002.003
46

41
003.002.003.001
49

41
003.002.003.002
50

41
003.002.003.002.001
51

41
003.003
47

81
004.001
82

81
004.001.001
83

81
004.001.001.001
84

81
004.001.001.001.001
85

91
005.001
92


-- Full data in working table (for reference)

SELECT * FROM #summary_table ORDER BY Hierarchy;


Master_Father_ID
Current_ID
Hierarchy
insert_round


1
1
001
1

1
2
001.001
2

1
3
001.002
2

1
4
001.002.001
3

1
5
001.002.002
3

1
6
001.002.003
3

1
7
001.003
2

21
21
002
1

21
22
002.001
2

21
23
002.002
2

21
24
002.002.001
3

21
25
002.002.002
3

21
26
002.002.003
3

21
27
002.003
2

41
41
003
1

41
42
003.001
2

41
43
003.002
2

41
44
003.002.001
3

41
45
003.002.002
3

41
47
003.002.002.001
4

41
48
003.002.002.002
4

41
46
003.002.003
3

41
49
003.002.003.001
4

41
50
003.002.003.002
4

41
51
003.002.003.002.001
5

41
47
003.003
2

81
81
004
1

81
82
004.001
2

81
83
004.001.001
3

81
84
004.001.001.001
4

81
85
004.001.001.001.001
5

91
91
005
1

91
92
005.001
2

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
21	002.001	22
21	002.002	23
21	002.002.001	24
21	002.002.002	25
21	002.002.003	26
21	002.003	27
41	003.001	42
41	003.002	43
41	003.002.001	44
41	003.002.002	45
41	003.002.002.001	47
41	003.002.002.002	48
41	003.002.003	46
41	003.002.003.001	49
41	003.002.003.002	50
41	003.002.003.002.001	51
41	003.003	47
81	004.001	82
81	004.001.001	83
81	004.001.001.001	84
81	004.001.001.001.001	85
91	005.001	92