Recursive algorithm for category

  • Hi all,

    I have a table Category(CategoryID int,ParentCategoryID int, Name nvarchar(1000)).

    CategoryID ParentCategoryID Name

    1 NULL A

    2 1 B

    3 1 C

    4 2 D

    5 2 E

    6 3 F

    7 1 G

    8 5 H

    9 7 I

    10 8 J

    Now I would like to retrieve data as same as

    CategoryID Name NameNavigation

    1 A NULL

    2 B A -> B

    3 C A -> C

    4 D A -> B -> D

    5 E A -> B -> E

    6 F A -> C -> F

    7 G A -> G

    8 H A -> B -> E -> H

    9 I A -> G -> I

    10 J A -> B -> E -> H -> J

    Please help me with any suggestion. I need a generic algorithm to process without limiting depth (level) of category.

    Thanks,

  • You might want to look at this thread as its very similar to what you want to do,

    http://www.sqlservercentral.com/Forums/Topic1379719-391-1.aspx

    The only difference is the source table and column names and the format of the output string in the CTE, so you should be able to recode this to suite your requirements.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • This was removed by the editor as SPAM

  • Jason-299789 (11/7/2012)


    You might want to look at this thread as its very similar to what you want to do,

    http://www.sqlservercentral.com/Forums/Topic1379719-391-1.aspx

    The only difference is the source table and column names and the format of the output string in the CTE, so you should be able to recode this to suite your requirements.

    I got the idea to do my situation.

    Thanks so much,

  • DECLARE @Category TABLE(CategoryID int,ParentCategoryID int, Name nvarchar(1000))

    INSERT INTO @Category(CategoryID,ParentCategoryID,Name)

    VALUES

    (1 , NULL, 'A' ),

    (2 , 1 , 'B' ),

    (3 , 1 , 'C' ),

    (4 , 2 , 'D' ),

    (5 , 2 , 'E' ),

    (6 , 3 , 'F' ),

    (7 , 1 , 'G' ),

    (8 , 5 , 'H' ),

    (9 , 7 , 'I' ),

    (10, 8 , 'J' );

    WITH Recur AS (

    SELECT CategoryID, ParentCategoryID, Name, Name AS NameNavigation

    FROM @Category

    UNION ALL

    SELECT r.CategoryID, c.ParentCategoryID, r.Name, CAST(c.Name + N' -> ' + r.NameNavigation AS nvarchar(1000))

    FROM @Category c

    INNER JOIN Recur r ON r.ParentCategoryID = c.CategoryID

    )

    SELECT CategoryID,Name,NameNavigation

    FROM Recur

    WHERE ParentCategoryID IS NULL

    ORDER BY CategoryID;

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Mark-101232 (11/7/2012)


    DECLARE @Category TABLE(CategoryID int,ParentCategoryID int, Name nvarchar(1000))

    INSERT INTO @Category(CategoryID,ParentCategoryID,Name)

    VALUES

    (1 , NULL, 'A' ),

    (2 , 1 , 'B' ),

    (3 , 1 , 'C' ),

    (4 , 2 , 'D' ),

    (5 , 2 , 'E' ),

    (6 , 3 , 'F' ),

    (7 , 1 , 'G' ),

    (8 , 5 , 'H' ),

    (9 , 7 , 'I' ),

    (10, 8 , 'J' );

    WITH Recur AS (

    SELECT CategoryID, ParentCategoryID, Name, Name AS NameNavigation

    FROM @Category

    UNION ALL

    SELECT r.CategoryID, c.ParentCategoryID, r.Name, CAST(c.Name + N' -> ' + r.NameNavigation AS nvarchar(1000))

    FROM @Category c

    INNER JOIN Recur r ON r.ParentCategoryID = c.CategoryID

    )

    SELECT CategoryID,Name,NameNavigation

    FROM Recur

    WHERE ParentCategoryID IS NULL

    ORDER BY CategoryID;

    Yes, I got it 🙂

  • By the way, do we have any solution to improve performance of CTE in case the category tree has depth > 4?

    As I knew, if we use CTE to do recursive algorithm, SQL engine must read so many times. In my case, there are ~4000 categories and max of depth = 4, and I run CTE

    Table 'Category'. Scan count 2, logical reads 2543467, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 2, logical reads 27561, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Thanks,

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply