T-SQL TreePath

The table contains a data sample that holds hierarchically linked records. The concept of hierarchy informs us that the entire data is a tree structure.

In this example, we will display the name of any row by combining it with the names of the higher-level rows. So we will join the branches of the tree.

For Example;

• For the line with id 4; “Parent 1 -> Child 1.1 -> Child 1.1.1” value
• For the line with id 2; Set the value “Parent 1 -> Child 1.1”
• For the line with id 1; “Parent 1” value

We can use the following SQL Script for getting this data result.

WITH p AS
(
SELECT Id,ParentId,Ad,cast(Ad as varchar(max)) as treepath
FROM ParentChild WHERE parentID is null
UNION ALL
SELECT c.Id,c.ParentId,c.Ad,cast(p.treepath +'->'+cast(c.Ad as varchar(max)) as varchar(max)) as nihaipath
FROM ParentChild c JOIN p ON p.Id = c.ParentId
)
SELECT * FROM p

And result is this.