Get path of node in tree

Question

I want get path of specific node in my tree. Please see my tree data.

DECLARE @TT TABLE 
(
Id int,
Name varchar(50),
Parent int
)

INSERT @TT 
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 specific id ? for example for id = 5, result is :

 Great GrandFather Thomas Bishop -> Grand Mom Elian Thomas Wilson -> Aunt Nancy Manor

Answer 1

Try this one -

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

INSERT @temp (Id, Name, Parent) 
VALUES
    (1, 'Great GrandFather Thomas Bishop', NULL),
    (2, 'Grand Mom Elian Thomas Wilson' , 1),
    (3, 'Dad James Wilson',2),
    (4, 'Uncle Michael Wilson', 2),
    (5, 'Aunt Nancy Manor', 2),
    (6, 'Grand Uncle Michael Bishop', 1),
    (7, 'Brother David James Wilson', 3),
    (8, 'Sister Michelle Clark', 3),
    (9, 'Brother Robert James Wilson', 3),
    (10, 'Me Steve James Wilson', 3) 

;WITH cte AS 
(
    SELECT *, t = 1
    FROM @temp
    WHERE Id = 5 -- <-- 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, '')

Answer 2

with data (id, name, parent) as (
    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 
), cte as (
    select *, cast(name as varchar(8000)) as path
    from data
    where parent is null

    union all

    select d.id, d.name, d.parent, isnull(path + ' -> ', '') + d.name
    from cte
    inner join data d on cte.id = d.parent
)
select *
from cte