;with cte_t AS ( SELECT T1.pk_rights, t1.fk_rights, T1.RightsName, 0 AS LEVEL, T1.RightsOrder, T1.Description AS ChildDescr, CAST(RIGHT('000'+CAST(t1.RightsOrder AS VARCHAR(3)),2) AS VARCHAR(50)) AS ro FROM @Test T1 WHERE T1.fk_rights IS NULL UNION all SELECT T1.pk_rights, T1.fk_rights, T1.RightsName, LEVEL + 1 AS LEVEL, T1.RightsOrder, T1.Description AS ChildDescr, CAST(D.ro + '-' + RIGHT('000'+CAST(t1.RightsOrder AS VARCHAR(3)),2) AS VARCHAR(50)) AS ro FROM @Test T1 INNER join cte_t D ON T1.fk_rights = D.pk_rights ) SELECT cte.pk_rights, cte.fk_rights, LEFT(REPLICATE(' ',cte.LEVEL)+cte.RightsName,50) AS RightsName, LEFT(REPLICATE(' ',cte.LEVEL)+CAST(RightsOrder AS VARCHAR(5)),15) AS RightsOrder, LEFT(cte.ChildDescr,50) AS [Description] FROM cte_t cte ORDER BY ro>If I look at (1) the desired output see message#1422528 and (2) your order by - I'd be surprised