Get specific nodes path from root tsql

Question

I want set data in table with tree structure.

DECLARE @temp TABLE 
(
      Id INT
    , Name VARCHAR(50)
    , Parent INT
)

INSERT @temp 
SELECT 1,' Great GrandFather Thomas Bishop', null UNION ALL
SELECT 2,'Grand Mom Elian Thomas Wilson' , 1 UNION ALL
SELECT 3, 'Dad James Wilson',2 UNION ALL
SELECT 4, 'Uncle Michael Wilson', 2 UNION ALL
SELECT 5, 'Aunt Nancy Manor', 2 UNION ALL
SELECT 6, 'Grand Uncle Michael Bishop', 1 UNION ALL
SELECT 7, 'Brother David James Wilson',3 UNION ALL
SELECT 8, 'Sister Michelle Clark', 3 UNION ALL
SELECT 9, 'Brother Robert James Wilson', 3 UNION ALL
SELECT 10, 'Me Steve James Wilson', 3 

How can I get path of node from root for specific nodes?

For example result for Id IN (2, 5, 10) is :

Id   Result
 2   Great GrandFather Thomas Bishop -> Grand Mom Elian Thomas Wilson
 5   Great GrandFather Thomas Bishop -> Grand Mom Elian Thomas Wilson -> Aunt Nancy Manor
10   Great GrandFather Thomas Bishop -> Grand Mom Elian Thomas Wilson -> Dad James Wilson -> Me Steve James Wilson

For one id I use this T-SQL code, Please complete it :

;WITH cte AS 
(
    SELECT *, t = 1
    FROM @temp
    WHERE Id = 10 -- <-- your id

    UNION ALL

    SELECT t2.*, t + 1
    FROM cte t
    JOIN @temp t2 ON t.Parent = t2.Id
)
SELECT STUFF((
    SELECT ' -> ' + Name
    FROM cte
    ORDER BY t DESC
    FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 4, '')
Go

When I use FOR XML PATH('') speed is low, how can I use your T-SQL code without it?

Answer 1

How about this:

;WITH cte AS 
(
    SELECT *, t = 1, cast(name as varchar(max)) n2, id grp
    FROM @temp
    WHERE Id in (2,5,10) -- <-- your id

    UNION ALL

    SELECT t2.*, t + 1, coalesce(t2.name + ' -> ' + t.n2, t.n2), t.grp
    FROM cte t
    JOIN @temp t2 ON t.Parent = t2.Id
), cte2 as
(
SELECT grp, n2 result, row_number() over (partition by grp order by t desc) rn from cte

)
SELECT grp id, result from cte2 WHERE rn = 1

Result:

id  result
2    Great GrandFather Thomas Bishop -> Grand Mom Elian Thomas Wilson
5    Great GrandFather Thomas Bishop -> Grand Mom Elian Thomas Wilson -> Aunt Nancy Manor
10   Great GrandFather Thomas Bishop -> Grand Mom Elian Thomas Wilson -> Dad James Wilson -> Me Steve James Wilson