Amending the results order of a hierarchical query in SQL Server

Question

I have a table defining a hierarchy of ZONES as shown here:

:

Along with it, I'm using the following query:

WITH My_CTE(Zone, Parent, LEVEL , treepath) AS
    ( SELECT Zone_ID                            AS Zone   , 
             Parent_ID                          AS Parent , 
             0                                  AS LEVEL  ,
             ISNULL(CAST(Parent_ID  AS VARCHAR(1024)),'')  AS treepath
        FROM [UWQ].[T_SYS_Zones]
       WHERE Parent_ID IS NULL

    UNION ALL

    SELECT d.Zone_ID                            AS Zone   , 
           d.Parent_ID                          AS Parent ,
           My_CTE.LEVEL + 1                     AS LEVEL    ,
           CAST(My_CTE.treepath + ' > ' + CAST(d.Parent_ID AS VARCHAR(1024)) AS VARCHAR(1024)) AS treepath
           FROM [UWQ].[T_SYS_Zones] d

    INNER JOIN My_CTE
        ON My_CTE.Zone = d.Parent_ID
    )

SELECT *
  FROM My_CTE
 ORDER BY level , treepath;

This query does return the needed information except that I need it in a different order, meaning, each Zone is recursively listed with its children and children's children till exhaustion and only then the next brother is listed.

Using the data shown in the table, the result should be something like this:

Zone    Parent    LEVEL    treepath
0        NULL    0    
1        0        1         > 0
2        1        2         > 0 > 1
8        2        3         > 0 > 1 > 2
9        2        3         > 0 > 1 > 2
10       2        3         > 0 > 1 > 2
12       2        3         > 0 > 1 > 2
29       10       4         > 0 > 1 > 2 > 10
30       10       4         > 0 > 1 > 2 > 10
31       10       4         > 0 > 1 > 2 > 10
32       10       4         > 0 > 1 > 2 > 10
33       10       4         > 0 > 1 > 2 > 10
34       10       4         > 0 > 1 > 2 > 10
11       2        3         > 0 > 1 > 2
35       11       4         > 0 > 1 > 2 > 11
36       11       4         > 0 > 1 > 2 > 11
37       11       4         > 0 > 1 > 2 > 11
38       11       4         > 0 > 1 > 2 > 11
39       11       4         > 0 > 1 > 2 > 11
40       11       4         > 0 > 1 > 2 > 11
41       11       4         > 0 > 1 > 2 > 11
3        1        2         > 0 > 1
...

I tried playing with the shape of the treepath field with no success.

Answer 1

By adding an actual sequence, similar to your tree path, you can easily get the proper order/nesting. See the derived field SEQ

The @Top is optional, it allows you generate the tree from any node. NULL will be the full hierarchy.

You may notice the 100000+Row_Number() This is to ensure proper sequencing and avoids mis-orders like 1,10,11

Example

Declare @YourTable table (Zone_ID int,Parent_ID int)
Insert into @YourTable values 
(0, NULL),(1, 0),(2, 1),(8, 2),(9, 2),(10,2),(12,2),(29,10),(30,10),(31,10),(32,10),(33,10),(34,10),(11,2),(35,11),(36,11),(37,11),(38,11),(39,11),(40,11),(41,11),(3, 1)      

Declare @Top  int = null      --<<  Sets top of Hier Try 3 

;with cteP as (
      Select Seq  = cast(100000+Row_Number() over (Order by Zone_ID) as varchar(500))
            ,Zone_ID
            ,Parent_ID
            ,Level=1
            ,treepath = ISNULL(CAST(Parent_ID AS VARCHAR(1024)),'') 
      From   @YourTable 
      Where  IsNull(@Top,-1) = case when @Top is null then isnull(Parent_ID,-1) else Zone_ID end
      Union  All
      Select Seq  = cast(concat(p.Seq,'.',100000+Row_Number() over (Order by r.Zone_ID)) as varchar(500))
            ,r.Zone_ID
            ,r.Parent_ID
            ,p.Level+1
            ,CAST(p.treepath + ' > ' + CAST(r.Parent_ID AS VARCHAR(1024)) AS VARCHAR(1024))
      From   @YourTable r
      Join   cteP p on r.Parent_ID = p.Zone_ID)
Select Zone_ID
      ,Parent_ID
      ,Level
      ,treepath
 From cteP 
 Order By Seq

Returns