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.

Answer 2

Yet one more option is to use the hierarcyid data type

Example

Declare @YourTable Table ([ID] int,[Caption] varchar(50),[Parent] int)  Insert Into @YourTable Values 
 (1,'A',NULL)
,(2,'B',NULL)
,(3,'a',1)
,(4,'b',2)
,(5,'bb',4)
,(6,'C',NULL)
,(7,'aa',3)
,(8,'c',6)


;with cteP as (
      Select ID
            ,Parent 
            ,Caption 
            ,HierID = convert(hierarchyid,concat('/',ID,'/'))
      From   @YourTable 
      Where  Parent is null
      Union  All
      Select ID  = r.ID
            ,Parent  = r.Parent 
            ,Caption   = r.Caption
            ,HierID = convert(hierarchyid,concat(p.HierID.ToString(),r.ID,'/'))
      From   @YourTable r
      Join   cteP p on r.Parent  = p.ID)
Select Lvl   = HierID.GetLevel()
      ,ID
      ,Parent
      ,Caption 
 From cteP A
 Order By A.HierID

Returns

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

Answer 3

Such sort requires somehow traversing the hierarchy to compute the path of each node.

You can do this with a recursive query:

with cte as (
    select id, caption, parent, caption addr 
    from mytable
    where parent is null
    union all
    select t.id, t.caption, t.parent, c.addr + '/' + t.caption
    from cte c
    inner join mytable t on t.parent = c.id
)
select
    id,
    row_number() over(order by addr) sort,
    caption,
    parent
from c
order by addr

Answer 4

No need for recursion, just use some clever sorting!

SELECT
  ID,
  ROW_NUMBER() over(order by Caption) as Sort,
  Caption,
  Parent
FROM Address
ORDER BY Caption, Parent

SQL Fiddle: http://sqlfiddle.com/#!18/bbbbe/9