# 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,

Friday, June 9, 2017 2:30 AM

• 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```

• Proposed as answer by Friday, June 9, 2017 6:31 AM
• Marked as answer by Sunday, June 11, 2017 8:48 PM
Friday, June 9, 2017 2:48 AM
• Hi Gil,

About recursive CTE, you can refer to following articles:

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 MSDNFSF@microsoft.com.

• Marked as answer by Sunday, June 11, 2017 8:48 PM
Friday, June 9, 2017 8:59 AM

### All replies

• 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```

• Proposed as answer by Friday, June 9, 2017 6:31 AM
• Marked as answer by Sunday, June 11, 2017 8:48 PM
Friday, June 9, 2017 2:48 AM
• Hi Gil,

About recursive CTE, you can refer to following articles:

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 MSDNFSF@microsoft.com.

• Marked as answer by Sunday, June 11, 2017 8:48 PM
Friday, June 9, 2017 8:59 AM