SQL recursive display of hierarchical data

; withCatchild as(Select *,cast( Right('00000'+cast(Sort as varchar),5) as varchar(Max)) asSsort fromCategorywhereId= ' 123'    Union  All SelectT.*,    cast(Ssort+right('00000'+cast(T.sort as varchar),5) as varchar(Max))      fromcatchild, Category twhereCatchild.id=T.parentid)Select *  fromCatchildwhereIsvolumes='1'  Order  bySsort,sort; withCte as (SelectId,name,parentid,1  Level,         cast( Right('00000'+cast(Sort as varchar),5) as varchar(Max)) asSsort fromCategorywhereId= '111A'   Union  All SelectT.id,t.name,t.parentid,c. Level + 1,       cast(Ssort+right('00000'+cast(T.sort as varchar),5) as varchar(Max))     fromCategory TJoinCTE C onT.parentid=c.id)SelectID, Name, ParentID, Level, Ssort fromCteOrder  bySsort

SQL recursive display of hierarchical data