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 |