Parent and Child - Efficient way to add column

Question

Hi All,

Just wondering if there's a efficient way to add column just for the ROOT code. 

Input

Parent | Child

NULL | 1

1 | 2

1 | 3

2 | 4

3 | 5

5 | 6

Output

ROOT | Parent | Child

1 | NULL | 1

1 | 1 | 2

1 | 1 | 3

1 | 2 | 4

1 | 3 | 5

1 | 5 | 6

Got my desire result by using this code

DROP TABLE #Table1

CREATE TABLE #Table1 
(
	Parent VARCHAR(10),
	Child VARCHAR(10),
	[Name] VARCHAR(255)
)

INSERT INTO #Table1 (Parent, Child, [Name])
VALUES(NULL, 1, 'Name1'),
(1, 2, 'Name2'),
(1, 3, 'Name3'),
(2, 4, 'Name4'),
(2, 5, 'Name5'),
(4, 6, 'Name6'),
(3, 7, 'Name7')


;WITH t AS (
	SELECT Parent, Child, [Name]
	FROM #Table1
)
, xt AS (
	SELECT Parent, Child, [Name], 1 AS 'Level'
	FROM t
	WHERE Parent IS NULL

	UNION ALL
	
	SELECT y.Parent, y.Child, x.[Name], x.Level + 1
	FROM xt AS x
	INNER JOIN t AS y ON y.Parent = x.Child
	WHERE y.Parent IS NOT NULL	
)
SELECT b.Child AS ROOT, a.Parent, a.Child
FROM xt a
LEFT OUTER JOIN xt b ON a.[Name] = b.[Name]
WHERE b.Level = 1

Any suggestion would be appreciated.

Cheers,

Answer 1

Just carry it through, your initial cte also looks a touch superfluous

;WITH xt AS (
	SELECT Child as Root, Parent, Child, [Name], 1 AS 'Level'
	FROM #Table1
	WHERE Parent IS NULL

	UNION ALL
	
	SELECT Root, y.Parent, y.Child, x.[Name], x.Level + 1
	FROM xt AS x
	INNER JOIN #Table1 AS y ON y.Parent = x.Child
	WHERE y.Parent IS NOT NULL	
)
SELECT ROOT, a.Parent, a.Child
FROM xt a

Answer 2

Hi Gil,

About recursive CTE, you can refer to following articles:

Recursive Queries Using Common Table Expressions 

How to use recursive CTE calls in T-SQL 

SQL Server CTE Basics 

Thanks,
Xi Jin.

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].