SQL query order by top category follow by subcategory

Question

i have 1 category table which has parent_id field in it.. parent_id can contain category id so we know its sub catgeory. parent_id = 0 means it is top level category

i want to write a single query to order by top category first, then all subcategories of that top category, then all sub-subcategories of that top category. does any one has any idea on this.

Thanks

Sampe data

CategoryId      |   CategoryName    |   Parent_id
1                   cat A               0
2                   cat B               0
3                   cat C               0
4                   cat D               0
5                   cat A A             1
6                   cat A B             1
7                   cat A A A           5
8                   cat A A B           5
9                   cat B A             2
10                  cat B B             2
11                  cat B C             2
12                  cat B D             2

The result should be like:

CategoryId      |   CategoryName    |   Parent_id
1                   cat A               0
5                   cat A A             1
7                   cat A A A           5
8                   cat A A B           5
6                   cat A B             1
2                   cat B               0
9                   cat B A             2
10                  cat B B             2
11                  cat B C             2
12                  cat B D             2
3                   cat C               0
4                   cat D               0

Answer 1

Declare @YourTable table (categoryID int,CategoryName varchar(50),parent_id int)
Insert into @YourTable values 
(1 ,'cat A',    0),
(2 ,'cat B',    0),
(3 ,'cat C',    0),
(4 ,'cat D',    0),
(5 ,'cat A A',  1),
(6 ,'cat A B',  1),
(7 ,'cat A A A',5),
(8 ,'cat A A B',5),
(9 ,'cat B A',  2),
(10,'cat B B',  2),
(11,'cat B C',  2),
(12,'cat B D',  2)

Declare @Top  int = null             --<<  Sets top of Hier Try 5
Declare @Nest varchar(25) ='|-----'  --<<  Optional: Added for readability

;with ctePt as (
      Select Seq  = cast(1000+Row_Number() over (Order by CategoryName) as varchar(500))
            ,categoryID
            ,parent_id
            ,Lvl=1
            ,CategoryName 
      From   @YourTable 
      Where  IsNull(@Top,0) = case when @Top is null then isnull(parent_id,0) else categoryID end
      Union  All
      Select Seq  = cast(concat(p.Seq,'.',100000+Row_Number() over (Order by r.CategoryName)) as varchar(500))
            ,r.categoryID
            ,r.parent_id,p.Lvl+1
            ,r.CategoryName 
      From   @YourTable r 
      Join   ctePt p  on r.parent_id = p.categoryID)
     ,cteR1 as (Select Seq,categoryID,R1=Row_Number() over (Order By Seq) From ctePt)
     ,cteR2 as (Select A.Seq,A.categoryID,R2=Max(B.R1) From cteR1 A Join cteR1 B on (B.Seq like A.Seq+'%') Group By A.Seq,A.categoryID )
Select B.R1  
      ,C.R2
      ,A.categoryID
      ,A.parent_id
      ,A.Lvl
      ,CategoryName = Replicate(@Nest,A.Lvl-1) + A.CategoryName
 From ctePt A
 Join cteR1 B on A.categoryID=B.categoryID
 Join cteR2 C on A.categoryID=C.categoryID
 Order By B.R1

Returns

Now, you may notice R1/R2. These are the Range Keys. I find them useful in navigating and aggregated data up the hierarchy. If you don't need them, remove cteR1 and cteR2 and set the final Order By A.Seq

Just for fun, if you set @Top=5, you'll get