Hi,

I do not know if I understand correctly, but try:

with CteRec as
(
    select
        c.ID,
        c.CategoryName,
        c.DisplayOrder as ParentDisplayOrder,
        0 as DisplayOrder,
        c.ParentCategoryID
    from @tblCategory as c
    where c.ParentCategoryID is null
      
    union all
     
    select
        c.ID,
        c.CategoryName,
        r.ParentDisplayOrder as ParentDisplayOrder,
        c.DisplayOrder,
        c.ParentCategoryID
    from @tblCategory as c
    join CteRec as r on r.ID = c.ParentCategoryID
    where c.ParentCategoryID = 2
)

select * from CteRec
order by ParentDisplayOrder, DisplayOrder

I hope it's useful.