Recursive CTE query generate product or page hierarchy

Question

Page_ID     Page_Name               Page_Title              Page_Handler        Parent_Page_ID  Language_ID     Region_ID
1           Home                    Home                    index.aspx              0               1               uae
2           Personal                Personal                index.aspx              0               1               uae
3           Accounts & Deposits     Accounts & Deposits     index.aspx              2               1               uae
4           Current Account         Current Account         current-account.aspx    3               1               uae
5           Current Gold Accounts   gold Account            gold-account.aspx       3               1               uae
6           Easy Saver              Easy Saver Account      saver-account.aspx      3               1               uae
7           Fixed Deposits          Fixed Account           fixed-account.aspx      3               1               uae
8           Loans                   Loans                   index.aspx              2               1               uae
9           Personal Loans          Personal Loans          index.aspx              8               1               uae
10          car Loans               car Loans               car-loan.aspx           8               1               uae

I want to generate below path for asp.net routing i can use same for breadcrumb navigation also

www.abc.com/home
www.abc.com/personal
www.abc.com/personal/acounts-deposits/
www.abc.com/personal/acounts-deposits/current-account
www.abc.com/personal/acounts-deposits/current-gold-account
www.abc.com/personal/acounts-deposits/easy-saver
......
......
www.abc.com/personal/loans/
www.abc.com/personal/loans/personal-loans
www.abc.com/personal/loans/car-loans

I have modified a CTE script to work with a similar structure of my table. But problem with this CTE is that it give me as sibling/child/parent... while i need path as parent/child/sibling

Example http://sqlfiddle.com/#!3/0d086/1

SAMPLE CODE - due to limitaion on sqlfiddle i have used short data

CREATE TABLE PageMenu
    ([PageId] int, [PageName] varchar(5), [PageInheritance] int)
;

INSERT INTO PageMenu
    ([PageId], [PageName], [PageInheritance])
VALUES
    (1, 'home', 0),
    (2, 'p1', 0),
    (3, 'c1', 2),
    (4, 'c2', 2),
    (5, 's3', 4),
    (6, 'S3a', 5)
;
WITH CategoryCTE AS
(   SELECT  PageId, 
            PageName, 
            PageInheritance, 
            RecursionLevel = 1, 
            ParentRoot = CAST('None' AS VARCHAR(MAX)),
            LastParentCatID = PageInheritance
    FROM    PageMenu
    UNION ALL
    SELECT  cte.PageId, 
            cte.PageName,
            cte.PageInheritance,
            cte.RecursionLevel + 1,
            ParentRoot = CASE WHEN cte.RecursionLevel = 1 THEN '' ELSE cte.ParentRoot + '/' END + c.PageName,
            LastParentCatID = c.PageInheritance
    FROM    CategoryCTE cte
            INNER JOIN PageMenu c
                ON c.PageId = cte.LastParentCatID
), MaxRecursion AS
(   SELECT  PageId, 
            PageName, 
            PageInheritance, 
            ParentRoot, 
            RowNum = ROW_NUMBER() OVER(PARTITION BY PageId ORDER BY RecursionLevel DESC)
    FROM    CategoryCTE
)
SELECT  PageId, PageName, PageInheritance, ParentRoot
FROM    MaxRecursion
WHERE   RowNum = 1 ORDER BY PageId DESC;

Answer 1

I think that M. Ali's (with the corrections made in the comments) is more appropriate than your approach. Anyway, if you want just to correct your query, it would be as easy as changing the ParentRoot calculation from your recursive query to:

ParentRoot = CASE WHEN cte.ParentRoot <> 'None' THEN c.PageName + '/' + cte.ParentRoot ELSE c.PageName END,

So, your query would look like this:

    WITH CategoryCTE AS
(   SELECT  PageId, 
            PageName, 
            PageInheritance, 
            RecursionLevel = 1, 
            ParentRoot = CAST('None' AS VARCHAR(MAX)),
            LastParentCatID = PageInheritance
    FROM    PageMenu
    UNION ALL
    SELECT  cte.PageId, 
            cte.PageName,
            cte.PageInheritance,
            cte.RecursionLevel + 1,
            ParentRoot = CASE WHEN cte.ParentRoot <> 'None' THEN c.PageName + '/' + cte.ParentRoot ELSE c.PageName END,
            LastParentCatID = c.PageInheritance
    FROM    CategoryCTE cte
            INNER JOIN PageMenu c
                ON c.PageId = cte.LastParentCatID
), MaxRecursion AS
(   SELECT  PageId, 
            PageName, 
            PageInheritance, 
            ParentRoot, 
            RowNum = ROW_NUMBER() OVER(PARTITION BY PageId ORDER BY RecursionLevel DESC)
    FROM    CategoryCTE
)
SELECT  PageId, PageName, PageInheritance, ParentRoot
FROM    MaxRecursion
WHERE   RowNum = 1 
ORDER BY PageId DESC;

UPDATE: This is Ali's answer corrected to show the results as the question's author wants to, and even simplified:

;with cte 
as (
    select
    t.PageInheritance, 1 as Level, 
    cast(t.Pagename as nvarchar(max)) AS [Path]
    from PageMenu as t

    union all

    select
    t.PageInheritance, Level + 1 as Level,
    cast(t.PageName as nvarchar(max)) + '/' + c.[Path] as [Path] 
    from PageMenu as t  
    inner join cte as c on c.PageInheritance = t.PageId
)
select N'www.abc.com/' + [Path] AS [Paths]
from cte 
WHERE PageInheritance = 0
order by [Level] ASC