Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Recursive algorithm for category Expand / Collapse
Author
Message
Posted Wednesday, November 7, 2012 1:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 3:08 AM
Points: 138, Visits: 934
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,
Post #1381824
Posted Wednesday, November 7, 2012 1:51 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1381830
Posted Wednesday, November 7, 2012 2:24 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:19 AM
Points: 3,917, Visits: 5,109
Have you tried using a recursive CTE
e.g.
;with CategoryDrillDown AS
(select CategoryID, ParentCategoryID, Name, convert(nvarchar(1000),NULL) AS ParentName
from Category
where ParentCategoryID is null
union all
select Category.CategoryID, Category.ParentCategoryID, Category.Name, CategoryDrillDown.Name AS parentName
from Category
join CategoryDrillDown on CategoryDrillDown.CategoryID = Category.ParentCategoryID)
select * from CategoryDrillDown



____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1381838
Posted Wednesday, November 7, 2012 2:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 3:08 AM
Points: 138, Visits: 934
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,
Post #1381846
Posted Wednesday, November 7, 2012 2:35 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:13 AM
Points: 1,694, Visits: 19,550

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;



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1381847
Posted Wednesday, November 7, 2012 2:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 3:08 AM
Points: 138, Visits: 934
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
Post #1381854
Posted Wednesday, November 7, 2012 7:57 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 3:08 AM
Points: 138, Visits: 934
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,
Post #1382236
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse