>>;with cte_t AS >> ( >> SELECT T1.RightsName, >> T1.pk_rights, >> t1.fk_rights, >> 0 AS LEVEL, >> >> Cast(null as varchar(max)) AS ParentName, >> >> T2.RightsOrder, >> T2.Description AS ChildDescr >> FROM @Test T1 >> INNER join @test T2 ON T1.pk_rights = T2.pk_rights >> WHERE T1.fk_rights IS NULL >> UNION all >> >> SELECT T1.RightsName, >> T1.pk_rights, >> >> T1.fk_rights, >> LEVEL + 1 AS LEVEL, >> >> D.RightsName AS ParentName, >> T1.RightsOrder, >> T1.Description AS ChildDescr >> FROM @Test T1 >> >> INNER join cte_t D ON T1.fk_rights = D.pk_rights >>) >>SELECT * FROM cte_t ORDER BY fk_rights, RightsOrder, LEVEL>>