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