Sql query for all ascendent of a node
Make sure that the anchor is set properly (need to be either the roots or the final leaves). Your current anchor set (the first set from the UNION ALL) is retrieving all records from the table, not just the border ones.

Since your relationships are through parent and not by child, the anchor should contain all records without parents. Then join recursively with each child.

DECLARE @table TABLE (ID INT, name VARCHAR(1), parentID INT)

INSERT INTO @table
VALUES 
    (1, 'A', NULL),
    (2, 'B', 1),
    (3, 'C', NULL),
    (4, 'D', 3),
    (5, 'E', 4)

;With CTE AS
(
    -- Anchor (all grandfathers)
    SELECT
        ID = T.ID,
        Name = T.Name,
        ParentName = CONVERT(VARCHAR(MAX), NULL) -- Set the data type for the UNION
    FROM
        @table AS T
    WHERE
        T.parentId IS NULL

    UNION ALL

    -- Traverse through each children
    SELECT
        ID = T.ID,
        Name = T.Name,
        ParentName = ISNULL(C.ParentName + ' -&gt; ', '') + C.Name
    FROM
        CTE AS C
        INNER JOIN @table AS T ON C.ID = T.parentId
)
SELECT 
    C.*
FROM 
    cte AS C
ORDER BY
    C.ID


Result:

ID  Name    ParentName
1   A       NULL
2   B       A
3   C       NULL
4   D       C
5   E       C -&gt; D