InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 02:23:47 GMT20http://www.sqlservercentral.com/Forums/Topic1381824-391-1.aspxBy 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 [quote]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.[/quote]Thanks,Wed, 07 Nov 2012 19:57:38 GMTDung Dinhhttp://www.sqlservercentral.com/Forums/Topic1381824-391-1.aspx[quote][b]Mark-101232 (11/7/2012)[/b][hr][code="sql"]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 NameNavigationFROM @CategoryUNION ALLSELECT r.CategoryID, c.ParentCategoryID, r.Name, CAST(c.Name + N' -> ' + r.NameNavigation AS nvarchar(1000))FROM @Category cINNER JOIN Recur r ON r.ParentCategoryID = c.CategoryID)SELECT CategoryID,Name,NameNavigationFROM RecurWHERE ParentCategoryID IS NULLORDER BY CategoryID;[/code][/quote]Yes, I got it :-)Wed, 07 Nov 2012 02:53:13 GMTDung Dinhhttp://www.sqlservercentral.com/Forums/Topic1381824-391-1.aspx[code="sql"]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 NameNavigationFROM @CategoryUNION ALLSELECT r.CategoryID, c.ParentCategoryID, r.Name, CAST(c.Name + N' -> ' + r.NameNavigation AS nvarchar(1000))FROM @Category cINNER JOIN Recur r ON r.ParentCategoryID = c.CategoryID)SELECT CategoryID,Name,NameNavigationFROM RecurWHERE ParentCategoryID IS NULLORDER BY CategoryID;[/code]Wed, 07 Nov 2012 02:35:54 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic1381824-391-1.aspx[quote][b]Jason-299789 (11/7/2012)[/b][hr]You might want to look at this thread as its very similar to what you want to do, [url]http://www.sqlservercentral.com/Forums/Topic1379719-391-1.aspx[/url]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.[/quote]I got the idea to do my situation. Thanks so much,Wed, 07 Nov 2012 02:35:39 GMTDung Dinhhttp://www.sqlservercentral.com/Forums/Topic1381824-391-1.aspxHave you tried using a [url=http://msdn.microsoft.com/en-us/library/ms186243(v=SQL.105).aspx]recursive CTE[/url]e.g.[code="sql"];with CategoryDrillDown AS(select CategoryID, ParentCategoryID, Name, convert(nvarchar(1000),NULL) AS ParentNamefrom Categorywhere ParentCategoryID is nullunion allselect Category.CategoryID, Category.ParentCategoryID, Category.Name, CategoryDrillDown.Name AS parentNamefrom Categoryjoin CategoryDrillDown on CategoryDrillDown.CategoryID = Category.ParentCategoryID)select * from CategoryDrillDown[/code]Wed, 07 Nov 2012 02:24:36 GMTStewart "Arturius" Campbellhttp://www.sqlservercentral.com/Forums/Topic1381824-391-1.aspxYou might want to look at this thread as its very similar to what you want to do, [url]http://www.sqlservercentral.com/Forums/Topic1379719-391-1.aspx[/url]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.Wed, 07 Nov 2012 01:51:26 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1381824-391-1.aspxHi all,I have a table Category(CategoryID int,ParentCategoryID int, Name nvarchar(1000)).[code="other"]CategoryID ParentCategoryID Name1 NULL A2 1 B3 1 C4 2 D5 2 E6 3 F7 1 G8 5 H9 7 I10 8 J[/code]Now I would like to retrieve data as same as[code="other"]CategoryID Name NameNavigation1 A NULL2 B A -> B3 C A -> C4 D A -> B -> D5 E A -> B -> E 6 F A -> C -> F7 G A -> G8 H A -> B -> E -> H9 I A -> G -> I10 J A -> B -> E -> H -> J[/code]Please help me with any suggestion. I need a generic algorithm to process without limiting depth (level) of category.Thanks,Wed, 07 Nov 2012 01:41:46 GMTDung Dinh