Hierarchical CTE - can not make it to work :( 1

[markros]

Hi everybody,

I could not figure out this problem - I need to show parents with their children ordered by RightsOrder

declare @Test table 
(pk_rights   Int, fk_rights  int null, RightsName   varchar(max),     RightsOrder smallint, Description varchar(max))
insert into @test select

1,           NULL,        'Open Category Library',                              0,           'Open Category Library'
union select
18   ,       NULL,        'Open Portfolio Footnote Library',                    1,           'Open Portfolio Footnote Library'
union select
2,           1,           'Add Category Library',                               0,           'Add Category Library'
union select
3,           1,           'Delete Category Library',                            1,           'Delete Category Library'
union select
11,          2,           'Edit Category Library',                              0,           'Edit Category Library'
union select
7,           2,           'Add Category',                                       1,           'Add Category'
union select
12,          2,           'Add Category Language',                              2,           'Add Category Language'
union select
14,          2,           'Add Category Caption',                               3,           'Add Category Caption'
union select
9,           2,           'Delete Category',                                    4,           'Delete Category'
union select
16,          2,           'Delete Category Caption',                            5,           'Delete Category Caption'
union select
17,          2,           'Delete Cateogry Language',                           6,           'Delete Cateogry Language'
union select
8           ,7,           'Edit Category',                                      0,           'Edit Category'
union select
13,          12,          'Edit Category Lanaguage',                            0,           'Edit Category Lanaguage'
union select
15          ,14          ,'EditCategory Caption',                               0,           'EditCategory Caption'


select * from @Test

;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, 
                D2.ChildName, D2.RightsOrder, D2.[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

 

[bborissov]

;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,
                D2.ChildName, T1.RightsOrder, D2.[Description] as ChildDescr from @Test T1
                inner join cte_t D2 on T1.fk_rights = D2.pk_rights
                )
select * from cte_t order by ISNULL(fk_rights,pk_rights), RightsOrder, Level

?????

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bborissov]

or maybe:

[COLOR=blue]declare[/color] @Test [COLOR=blue]table[/color] (pk_rights   [COLOR=blue]Int[/color],
                     fk_rights   [COLOR=blue]int[/color] null,
                     RightsName  [COLOR=blue]varchar[/color]([COLOR=#FF00FF]max[/color]),
                     RightsOrder [COLOR=blue]smallint[/color],
                     Description [COLOR=blue]varchar[/color]([COLOR=#FF00FF]max[/color]))
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @test
[COLOR=blue]select[/color]  1, NULL, [COLOR=red]'Open Category Library'[/color]          , 0, [COLOR=red]'Open Category Library'[/color]
union
[COLOR=blue]select[/color] 18, NULL, [COLOR=red]'Open Portfolio Footnote Library'[/color], 1, [COLOR=red]'Open Portfolio Footnote Library'[/color]
union
[COLOR=blue]select[/color]  2,    1, [COLOR=red]'Add Category Library'[/color]           , 0,[COLOR=red]'Add Category Library'[/color]
union
[COLOR=blue]select[/color]  3,    1, [COLOR=red]'Delete Category Library'[/color]        , 1,[COLOR=red]'Delete Category Library'[/color]
union
[COLOR=blue]select[/color] 11,    2, [COLOR=red]'Edit Category Library'[/color]          , 0,[COLOR=red]'Edit Category Library'[/color]
union
[COLOR=blue]select[/color]  7,    2, [COLOR=red]'Add Category'[/color]                   , 1,[COLOR=red]'Add Category'[/color]
union
[COLOR=blue]select[/color] 12,    2, [COLOR=red]'Add Category Language'[/color]          , 2,[COLOR=red]'Add Category Language'[/color]
union
[COLOR=blue]select[/color] 14,    2, [COLOR=red]'Add Category Caption'[/color]           , 3,[COLOR=red]'Add Category Caption'[/color]
union
[COLOR=blue]select[/color]  9,    2, [COLOR=red]'Delete Category'[/color]                , 4,[COLOR=red]'Delete Category'[/color]
union
[COLOR=blue]select[/color] 16,    2, [COLOR=red]'Delete Category Caption'[/color]        , 5,[COLOR=red]'Delete Category Caption'[/color]
union
[COLOR=blue]select[/color] 17,    2, [COLOR=red]'Delete Cateogry Language'[/color]       , 6,[COLOR=red]'Delete Cateogry Language'[/color]
union
[COLOR=blue]select[/color]  8,    7, [COLOR=red]'Edit Category'[/color]                  , 0,[COLOR=red]'Edit Category'[/color]
union
[COLOR=blue]select[/color] 13,   12, [COLOR=red]'Edit Category Lanaguage'[/color]        , 0, [COLOR=red]'Edit Category Lanaguage'[/color]
union
[COLOR=blue]select[/color] 15 ,  14, [COLOR=red]'EditCategory Caption'[/color]           , 0, [COLOR=red]'EditCategory Caption'[/color]


[COLOR=blue]select[/color] * [COLOR=blue]from[/color] @Test

;with cte_t [COLOR=blue]as[/color] ([COLOR=blue]select[/color] T1.RightsName, T1.[Description], T1.pk_rights, T1.fk_rights, 0 [COLOR=blue]as[/color] [COLOR=#FF00FF]Level[/color],
                        T1.RightsName [COLOR=blue]as[/color] ChildName, T1.RightsOrder, T1.[Description] [COLOR=blue]as[/color] ChildDescr,
                        [COLOR=#FF00FF]CAST[/color]([COLOR=#FF00FF]RIGHT[/color]([COLOR=red]'00000'[/color]+[COLOR=#FF00FF]CAST[/color](Pk_Rights [COLOR=blue]as[/color] [COLOR=blue]varchar[/color](5)),5) [COLOR=blue]as[/color] [COLOR=blue]varchar[/color]([COLOR=#FF00FF]max[/color])) [COLOR=blue]AS[/color] OrderBy
                [COLOR=blue]from[/color] @Test T1 [COLOR=blue]where[/color] T1.fk_rights [COLOR=blue]IS[/color] NULL
                
                union all
                [COLOR=blue]select[/color] T1.RightsName, T1.[Description], T1.pk_rights, t1.fk_rights, [COLOR=#FF00FF]Level[/color] + 1 [COLOR=blue]as[/color] [COLOR=#FF00FF]Level[/color],
                D2.ChildName, T1.RightsOrder, D2.[Description] [COLOR=blue]as[/color] ChildDescr,
                D2.OrderBy + [COLOR=#FF00FF]RIGHT[/color]([COLOR=red]'00000'[/color]+[COLOR=#FF00FF]CAST[/color](T1.RightsOrder [COLOR=blue]as[/color] [COLOR=blue]varchar[/color](5)),5) [COLOR=blue]AS[/color] OrderBy
                [COLOR=blue]from[/color] @Test T1
                [COLOR=blue]inner[/color] [COLOR=blue]join[/color] cte_t D2 [COLOR=blue]on[/color] T1.fk_rights = D2.pk_rights
                )
[COLOR=blue]select[/color] * [COLOR=blue]from[/color] cte_t [COLOR=blue]order[/color] [COLOR=blue]by[/color] OrderBy

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.