Sort Hierarchical table in SQL Server?

Question

I have a table Address and I want to sort rows like parent-1 => all-child-parent-1, parent-2 => all-child-parent-2 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   Sort  Caption   Parent
---------------------------
1    1     A         NULL
3    2     a         1
7    3     aaa       3
2    4     B         NULL
4    5     b         2
5    6     bb        4
6    7     C         NULL
8    8     c         6

Answer 1

You can construct the path to each row and then use that for sorting. The construction uses a recursive CTE:

with cte as (
      select id, caption, parent, convert(varchar(max), format(id, '0000')) as path, 1 as lev
      from t
      where parent is null
      union all
      select t.id, t.caption, t.parent, convert(varchar(max), concat(path, '->', format(t.id, '0000'))), lev + 1
      from cte join
           t
           on cte.id = t.parent
     )
select id, caption, parent
from cte
order by path;

Here is a db<>fiddle.