Yes, I deleted that solution - still working on it :(((((((((

;with cte_t AS (SELECT T1.RightsName, T1.[Description], T1.pk_rights, T1.fk_rights, 0 AS LEVEL,
                        T2.RightsName AS ChildName, 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.[Description], T1.pk_rights, t1.fk_rights, LEVEL + 1 AS LEVEL,
                D1.RightsName AS ChildName, D1.RightsOrder, D1.[Description] AS ChildDescr FROM @Test T1
                INNER join cte_t D1 ON T1.fk_rights = D1.pk_rights
                )
    
select pk_rights, fk_rights, RightsName, ChildName, RightsOrder, Description, ChildDescr, Level from cte_t order by Level, RightsOrder                    

>Naomi,
>
>Your solution only gave me the list as it was posted. I made some corrections and here is the starting list.
>
>

>pk_rights   fk_rights   RightsName                                         RightsOrder Description
>----------- ----------- -------------------------------------------------- ----------- --------------------------------------------------
>1           NULL        Open Category Library                              0           Open Category Library
>18          NULL        Open Portfolio Footnote Library                    1           Open Portfolio Footnote Library
>2           1           Add Category Library                               0           Add Category Library
>3           1           Delete Category Library                            1           Delete Category Library
>11          2           Edit Category Library                              0           Edit Category Library
>7           2           Add Category                                       2           Add Category
>12          2           Add Category Language                              4           Add Category Language
>14          2           Add Category Caption                               6           Add Category Caption
>9           2           Delete Category                                    3           Delete Category
>16          2           Delete Category Caption                            7           Delete Category Caption
>17          2           Delete Category Language                           5           Delete Category Language
>8           7           Edit Category                                      0           Edit Category
>13          12          Edit Category Lanaguage                            0           Edit Category Lanaguage
>15          14          EditCategory Caption                               0           EditCategory Caption
>

>
>
>I need the output to look like this.... the indents are for readability only but the rows must be ordered in this sequence.
>
>
>

>pk_rights   fk_rights   RightsName                                         RightsOrder Description
>----------- ----------- -------------------------------------------------- ----------- --------------------------------------------------
>1           NULL        Open Category Library                              0           Open Category Library
>2           1              Add Category Library                               0        Add Category Library
>11          2                 Edit Category Library                              0     Edit Category Library
>3           1              Delete Category Library                            1        Delete Category Library
>7           2              Add Category                                       2        Add Category
>8           7                 Edit Category                                      0     Edit Category
>9           2              Delete Category                                    3        Delete Category
>12          2              Add Category Language                              4        Add Category Language
>13          12                Edit Category Lanaguage                            0     Edit Category Lanaguage
>17          2              Delete Cateogry Language                           5        Delete Cateogry Language
>14          2              Add Category Caption                               6        Add Category Caption
>15          14                EditCategory Caption                               0     EditCategory Caption
>16          2              Delete Category Caption                            7        Delete Category Caption
>18          NULL        Open Portfolio Footnote Library                    1           Open Portfolio Footnote Library
>