InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 13:12:04 GMT20http://www.sqlservercentral.com/Forums/Topic971441-391-1.aspx[quote][b]RBarryYoung (5/26/2012)[/b][hr][quote][b]Jeff Moden (5/26/2012)[/b][hr]You mean just as a separate post or here or did you have something else in mind, Barry?[/quote]Right, a separate post, with a data set (million rows, of course) and a formal statement of the challenge ("fastest execution time to produce result set like this ...".I actually have a fair start on the data set...[/quote]Got it. I'll see what i can do. Thanks, Barry.Sun, 27 May 2012 08:49:26 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic971441-391-1.aspx[quote][b]Jeff Moden (5/26/2012)[/b][hr]You mean just as a separate post or here or did you have something else in mind, Barry?[/quote]Right, a separate post, with a data set (million rows, of course) and a formal statement of the challenge ("fastest execution time to produce result set like this ...".I actually have a fair start on the data set...Sat, 26 May 2012 15:18:54 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic971441-391-1.aspxYou mean just as a separate post or here or did you have something else in mind, Barry?Sat, 26 May 2012 14:06:09 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic971441-391-1.aspx[quote][b]Jeff Moden (5/24/2012)[/b][hr][quote][b]dwain.c (5/24/2012)[/b][hr][quote][b]Jeff Moden (10/24/2010)[/b][hr]For the simple task of sorting in the correct order, we'll use the "Hierarchical Path" method. To build it, we'll use a "Recursive CTE". Look that up in Books Online for a better understanding of them. I will tell you right now that they are NOT a panacea. Hierarchies are one of the few places where I don't mind the use of recursive CTE's because they're normally RBAR code. In the example that follows, the recursive CTE is "layered" or (as Celko calls it), "lasagna" code. It processes a full set (one full level) of information for each iteration which makes it non-RBAR code. It's still a bit slower than a more lengthy method but it takes a huge hierarchy to really appreciate the difference so the recursive CTE is usually good enough.[/quote]Oh my! No fair! I love posting recursive CTE solutions and you beat me to it.[/quote]Only by a year and eight months. :-) (Just having a little fun here).Shifting gears, Dwaine, all is not lost here. If you could figure out a way to make this kind of "lasagne" recursive CTE faster, I know a lot of people (especially me) that would greatly appreciate it. The only other way that I know to build the Hierarchical path is with a slightly faster While Loop and even though it's not a RBAR While Loop, it still doesn't sit so well with me that I have to use one here.[/quote]Jeff, could you post this as a challenge, along with a test set? I'd love to take a whack at it.Sat, 26 May 2012 13:50:45 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic971441-391-1.aspx[quote][b]dwain.c (5/24/2012)[/b][hr][quote][b]Jeff Moden (5/24/2012)[/b][hr][quote][b]dwain.c (5/24/2012)[/b][hr]...so what makes you think I could do so here?[/quote]Not sure. You DO have a love for rCTEs and I'm thinking that you'll pull a rabbit out of your hat just because you do love working with them.[/quote]Actually I think you're confusing my exploration with love.I have kind of a fondness/hate relationship with them.I'm fond of them because they're cool and interesting. I hate them because mostly they don't perform so well and because I am recursively challenged. And also because I haven't found that killer app yet (but will keep on looking).[/quote]I'm recursively challenged, as well. I've been married more than once. :-PFri, 25 May 2012 17:46:57 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic971441-391-1.aspx[quote][b]Jeff Moden (5/24/2012)[/b][hr][quote][b]dwain.c (5/24/2012)[/b][hr]...so what makes you think I could do so here?[/quote]Not sure. You DO have a love for rCTEs and I'm thinking that you'll pull a rabbit out of your hat just because you do love working with them.[/quote]Actually I think you're confusing my exploration with love.I have kind of a fondness/hate relationship with them.I'm fond of them because they're cool and interesting. I hate them because mostly they don't perform so well and because I am recursively challenged. And also because I haven't found that killer app yet (but will keep on looking).Thu, 24 May 2012 23:00:30 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic971441-391-1.aspx[quote][b]dwain.c (5/24/2012)[/b][hr]...so what makes you think I could do so here?[/quote]Not sure. You DO have a love for rCTEs and I'm thinking that you'll pull a rabbit out of your hat just because you do love working with them.Thu, 24 May 2012 22:52:51 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic971441-391-1.aspx[quote][b]Jeff Moden (5/24/2012)[/b][hr][quote][b]dwain.c (5/24/2012)[/b][hr][quote][b]Jeff Moden (10/24/2010)[/b][hr]For the simple task of sorting in the correct order, we'll use the "Hierarchical Path" method. To build it, we'll use a "Recursive CTE". Look that up in Books Online for a better understanding of them. I will tell you right now that they are NOT a panacea. Hierarchies are one of the few places where I don't mind the use of recursive CTE's because they're normally RBAR code. In the example that follows, the recursive CTE is "layered" or (as Celko calls it), "lasagna" code. It processes a full set (one full level) of information for each iteration which makes it non-RBAR code. It's still a bit slower than a more lengthy method but it takes a huge hierarchy to really appreciate the difference so the recursive CTE is usually good enough.[/quote]Oh my! No fair! I love posting recursive CTE solutions and you beat me to it.[/quote]Only by a year and eight months. :-) (Just having a little fun here).Shifting gears, Dwaine, all is not lost here. If you could figure out a way to make this kind of "lasagne" recursive CTE faster, I know a lot of people (especially me) that would greatly appreciate it. The only other way that I know to build the Hierarchical path is with a slightly faster While Loop and even though it's not a RBAR While Loop, it still doesn't sit so well with me that I have to use one here.[/quote]I have yet to beat one of your solutions in a timing competition, so what makes you think I could do so here? Not for lack of trying mind you.Seriously, I will continue to try. I am still looking for that "killer app" for recursive CTEs that no one has thought of before.Thu, 24 May 2012 21:55:39 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic971441-391-1.aspx[quote][b]dwain.c (5/24/2012)[/b][hr][quote][b]Jeff Moden (10/24/2010)[/b][hr]For the simple task of sorting in the correct order, we'll use the "Hierarchical Path" method. To build it, we'll use a "Recursive CTE". Look that up in Books Online for a better understanding of them. I will tell you right now that they are NOT a panacea. Hierarchies are one of the few places where I don't mind the use of recursive CTE's because they're normally RBAR code. In the example that follows, the recursive CTE is "layered" or (as Celko calls it), "lasagna" code. It processes a full set (one full level) of information for each iteration which makes it non-RBAR code. It's still a bit slower than a more lengthy method but it takes a huge hierarchy to really appreciate the difference so the recursive CTE is usually good enough.[/quote]Oh my! No fair! I love posting recursive CTE solutions and you beat me to it.[/quote]Only by a year and eight months. :-) (Just having a little fun here).Shifting gears, Dwaine, all is not lost here. If you could figure out a way to make this kind of "lasagne" recursive CTE faster, I know a lot of people (especially me) that would greatly appreciate it. The only other way that I know to build the Hierarchical path is with a slightly faster While Loop and even though it's not a RBAR While Loop, it still doesn't sit so well with me that I have to use one here.Thu, 24 May 2012 21:38:14 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic971441-391-1.aspx[quote][b]abr 70501 (5/24/2012)[/b][hr]AWESOME thanks so much for this post.It helped us out greatly![/quote]Very cool. Thanks for the feedback. Do you have any additional questions on this subject?Thu, 24 May 2012 21:22:30 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic971441-391-1.aspx[quote][b]Jeff Moden (10/24/2010)[/b][hr]For the simple task of sorting in the correct order, we'll use the "Hierarchical Path" method. To build it, we'll use a "Recursive CTE". Look that up in Books Online for a better understanding of them. I will tell you right now that they are NOT a panacea. Hierarchies are one of the few places where I don't mind the use of recursive CTE's because they're normally RBAR code. In the example that follows, the recursive CTE is "layered" or (as Celko calls it), "lasagna" code. It processes a full set (one full level) of information for each iteration which makes it non-RBAR code. It's still a bit slower than a more lengthy method but it takes a huge hierarchy to really appreciate the difference so the recursive CTE is usually good enough.[/quote]Oh my! No fair! I love posting recursive CTE solutions and you beat me to it.Thu, 24 May 2012 18:42:44 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic971441-391-1.aspxAWESOME thanks so much for this post.It helped us out greatly!Thu, 24 May 2012 17:05:12 GMTabr 70501http://www.sqlservercentral.com/Forums/Topic971441-391-1.aspxOh my. KSeet... allow me to apologize for 1.2 million members. You did absolutely everything right in posting table structure, data, desired results, etc, etc, and, still, no one helped unless you believed in the spam and bought a book.Joe Celko is correct... your data is an "Adjaceny List". He's also correct about "Nested Set" hierarchies being a bit easier to calculate with. There's another type of hierarchy called by many names (sometimes bad names in frustration if someone doesn't know how to work them :-P) but I usually refer to them as "Hierarchical Path" or "Sort Path". Each type has advantages and disadvantages depending on what you need to do. Note that "Hierarchical Path" is nearly identical to the HierarchyID in SQL Server 2008. I just don't have it on the box I'm working from today but building it takes code nearly identical to what I have below.For the simple task of sorting in the correct order, we'll use the "Hierarchical Path" method. To build it, we'll use a "Recursive CTE". Look that up in Books Online for a better understanding of them. I will tell you right now that they are NOT a panacea. Hierarchies are one of the few places where I don't mind the use of recursive CTE's because they're normally RBAR code. In the example that follows, the recursive CTE is "layered" or (as Celko calls it), "lasagna" code. It processes a full set (one full level) of information for each iteration which makes it non-RBAR code. It's still a bit slower than a more lengthy method but it takes a huge hierarchy to really appreciate the difference so the recursive CTE is usually good enough.Here's both your test data setup and the code to produce the output you asked for... I also modified the code to indent the items by level. You can, of course, easily remove that.[code="sql"]-- DROP TABLE #tblAsset;CREATE TABLE #tblAsset(ID int IDENTITY(1,1),AssetID bigint,ParentID bigint,Asset varchar(90),Node int);INSERT INTO #tblAsset(AssetID, ParentID,Asset,Node)VALUES(3319,3214,'Apartment',1);INSERT INTO #tblAsset(AssetID, ParentID,Asset,Node) VALUES( 3320,3319,'Building 1508',2);INSERT INTO #tblAsset(AssetID, ParentID,Asset,Node) VALUES( 3321,3319,'Building 1509',2);INSERT INTO #tblAsset(AssetID, ParentID,Asset,Node) VALUES( 3322,3319,'Building 1510',2);INSERT INTO #tblAsset(AssetID, ParentID,Asset,Node) VALUES(3323,3320,'Room 210',3);INSERT INTO #tblAsset(AssetID, ParentID,Asset,Node) VALUES( 3324,3320,'Room 211',3);WITH cteBuildPath AS (--==== This is the "anchor" part of the recursive CTE SELECT anchor.AssetID, anchor.ParentID, anchor.Asset, anchor.Node, 1 AS HLevel, CAST(CAST(anchor.AssetID AS BINARY(4)) AS VARBINARY(8000)) AS SortPath FROM #tblAsset AS anchor WHERE Node = 1 UNION ALL ------------------------------------------------------------------- --==== This is the "recursive" part of the CTE that adds 1 for each level -- and concatenates each level of AssetID's to the SortPath column. SELECT recur.AssetID, recur.ParentID, recur.Asset, recur.Node, cte.HLevel + 1 AS HLevel, CAST(cte.SortPath + CAST(Recur.AssetID AS BINARY(4)) AS VARBINARY(8000)) AS SortPath FROM #tblAsset AS recur INNER JOIN cteBuildPath AS cte ON cte.AssetID = recur.ParentID)--==== This final INSERT/SELECT creates the "narrow surrogate" column (NodeNumber) SELECT AssetID, ParentID, Asset = SPACE((Hlevel-1)*4)+Asset, Node --,SortPath FROM cteBuildPath ORDER BY SortPath;[/code]Here's the result...[code="plain"]AssetID ParentID Asset Node-------------------- -------------------- ------------------------------ -----------3319 3214 Apartment 13320 3319 Building 1508 23323 3320 Room 210 33324 3320 Room 211 33321 3319 Building 1509 23322 3319 Building 1510 2[/code]One final thing... notice the hierarchy level I calculated... it turns out to be the same as "Node". I don't know if "Node" was intended to be the "level", but it sure does look that way.Again... I'm not sure what happened and why no one reached out to help especially since you followed ALL the "rules" of posting on your very first post.Sun, 24 Oct 2010 23:39:40 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic971441-391-1.aspxYou are using an Adjacency List model. You might consider a Nested Sets Model instead, since it will do aggregation much easier and faster. You can Google it or get a copy of my TREES & HIERARCHIES IN SQL for details.Wed, 18 Aug 2010 14:41:09 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic971441-391-1.aspxI have a tree structure is like this - Apartment -Building 1508 - Room 210 - Room 211 - Building 1509 - Building 1510 I have output of a SQL query as ID |AssetID| ParentID|Asset Name | Node------------------------------------------------------------------ 1 3319 3214 Apartment 1 2 3320 3319 Building 1508 2 3 3321 3319 Building 1509 2 4 3322 3319 Building 1510 2 5 3323 3320 Room 210 3 6 3324 3320 Room 211 3 What I need in out put as below . Please suggest a SQL for this ID AssetID ParentID Asset Name Node------------------------------------------------------------------ 1 3319 3214 Apartment 1 2 3320 3319 Building 1508 2 3 3323 3320 Room 210 3 4 3324 3320 Room 211 3 5 3321 3319 Building 1509 2 6 3322 3319 Building 1510 2HERE is the script generate the data----------------------------------------------CREATE TABLE #tblAsset(ID int IDENTITY(1,1),AssetID bigint,ParentID bigint,Asset varchar(90),Node int)INSERT INTO #tblAsset(AssetID, ParentID,Asset,Node)VALUES(3319,3214,'Apartment',1)INSERT INTO #tblAsset(AssetID, ParentID,Asset,Node) VALUES( 3320,3319,'Building 1508',2)INSERT INTO #tblAsset(AssetID, ParentID,Asset,Node) VALUES( 3321,3319,'Building 1509',2)INSERT INTO #tblAsset(AssetID, ParentID,Asset,Node) VALUES( 3322,3319,'Building 1510',2)INSERT INTO #tblAsset(AssetID, ParentID,Asset,Node) VALUES(3323,3320,'Room 210',3)INSERT INTO #tblAsset(AssetID, ParentID,Asset,Node) VALUES( 3324,3320,'Room 211',3)SELECT * FROM #tblAssetDROP TABLE #tblAssetWill appriciate your helpWed, 18 Aug 2010 13:55:00 GMTKSeet