;with catChild as(select *,cast(right(''+cast(Sort as varchar),5) as varchar(max)) as ssort

from Category where ID = '123'

    union all select t.*,

    cast(ssort+right(''+cast(t.Sort as varchar),5) as varchar(max))

     from catChild, Category  t

    where catChild.ID = t.ParentID)

select * from catChild where IsVolumes=''  order by ssort,Sort

;with cte as

 (select ID,Name,ParentID,1 level,

         cast(right(''+cast(Sort as varchar),5) as varchar(max)) as ssort

    from Category where ID = '111A'

  union all select t.ID,t.Name,t.ParentID,c.level + 1,

       cast(ssort+right(''+cast(t.Sort as varchar),5) as varchar(max))

    from Category t

    join cte c on t.ParentID = c.ID)

select ID, Name, ParentID, level,ssort

  from cte order by ssort