Hierarchical query example in SQL Server

We have following records in our table.

We want output should come in this format.

This is best example of hierarchical query.
Let’s try to write it.

T-SQL

CREATE TABLE TableCTE (ID int, ParentID int NULL, Name varchar(128));
INSERT INTO TableCTE VALUES (1, NULL, 'A');
INSERT INTO TableCTE VALUES (2, 1, 'B');
INSERT INTO TableCTE VALUES (3, 2, 'C');
INSERT INTO TableCTE VALUES (4, 3, 'D');
INSERT INTO TableCTE VALUES (5, 4, 'E');
SELECT * FROM TableCTE
WITH Parent AS
(
    SELECT
        ID,
        ParentID,
        Name,
        Name AS Path
    FROM
        TableCTE
    WHERE
        ParentID IS NULL
    UNION ALL
    SELECT
        TH.ID,
        TH.ParentID,
        TH.Name,
        CONVERT(varchar(128), Parent.Path + ',' + TH.Name) AS Path
    FROM
        TableCTE TH
    INNER JOIN
        Parent
    ON
        Parent.ID = TH.ParentID
)
SELECT * FROM Parent

That’s It.
Enjoy Learning.