As far as I can see you can do the whole thing in a single, recursive query. Consider the following query using the test material you provided
WITH HQuery (ParentId, ChildId, Relation) AS (
SELECT t.ParentId,
t.ChildId,
CAST(t.ParentId AS varchar(100)) + ' - ' + CAST(t.ChildId AS varchar(100))
FROM Table1 t
WHERE t.ParentId = 3
UNION ALL
SELECT t.ParentId,
t.ChildId,
CAST(t.ParentId AS varchar(100)) + ' - ' + COALESCE(CAST(t.ChildId AS varchar(100)), 'NULL')
FROM Table1 t,
HQuery hq
WHERE t.ParentId = hq.ChildId
)
SELECT * FROM HQuery
For more information, have a look at
WITH common_table_expression (Transact-SQL) | Microsoft Docs[
^]