How to Concatenation Parent-Child Rows on Hierarchical table in SQL Server?

Question

I have a table Address and I want to Concatenation rows like parent-1 => parent-1/all-child, parent-2 => parent-2/all-child so on ....

Address Table

ID   Caption   Parent
---------------------
1    A          NULL
2    B          NULL
3    a          1
4    b          2
5    bb         4
6    C          NULL
7    aa         3
8    c          6

NULL Parent is is mean Root

Desired output

ID   Caption   Parent
---------------------
1    A          NULL
3    A/a        1
7    A/a/aa     3
2    B          NULL
4    B/b        2
5    B/b/bb     4
6    C          NULL
8    C/c        6

Answer 1

You can use a recursive cte for this. The idea is to start from the root nodes, and traverse the hierarchy towards the leafs, concatenating the path as you go.

with cte as (
    select id, cast(caption as nvarchar(max)) caption, parent from mytable where parent is null
    union all 
    select t.id, cast(c.caption + '/' + t.caption as nvarchar(max)), t.parent
    from cte c
    inner join mytable t on t.parent = c.id
)
select * from cte order by caption

Demo on DB Fiddle:

id | caption | parent
:- | :------ | -----:
1  | A       |   null
3  | A/a     |      1
7  | A/a/aa  |      3
2  | B       |   null
4  | B/b     |      2
5  | B/b/bb  |      4
6  | C       |   null
8  | C/c     |      6