Hierarchy Structure SQL Query

  • I 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

    ------------------------------------------------------------------

    133193214Apartment 1

    233203319Building 1508 2

    333213319Building 1509 2

    433223319Building 1510 2

    533233320Room 210 3

    633243320Room 211 3

    What I need in out put as below . Please suggest a SQL for this

    ID AssetID ParentID Asset Name Node

    ------------------------------------------------------------------

    133193214Apartment 1

    233203319Building 1508 2

    333233320Room 210 3

    433243320Room 211 3

    533213319Building 1509 2

    633223319Building 1510 2

    HERE 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 #tblAsset

    DROP TABLE #tblAsset

    Will appriciate your help

  • Oh 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.

    -- 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

    ;

    Here's the result...

    AssetID ParentID Asset Node

    -------------------- -------------------- ------------------------------ -----------

    3319 3214 Apartment 1

    3320 3319 Building 1508 2

    3323 3320 Room 210 3

    3324 3320 Room 211 3

    3321 3319 Building 1509 2

    3322 3319 Building 1510 2

    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.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • AWESOME thanks so much for this post.

    It helped us out greatly!

  • Jeff Moden (10/24/2010)


    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.

    Oh my! No fair! I love posting recursive CTE solutions and you beat me to it.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • abr 70501 (5/24/2012)


    AWESOME thanks so much for this post.

    It helped us out greatly!

    Very cool. Thanks for the feedback. Do you have any additional questions on this subject?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • dwain.c (5/24/2012)


    Jeff Moden (10/24/2010)


    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.

    Oh my! No fair! I love posting recursive CTE solutions and you beat me to it.

    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.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (5/24/2012)


    dwain.c (5/24/2012)


    Jeff Moden (10/24/2010)


    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.

    Oh my! No fair! I love posting recursive CTE solutions and you beat me to it.

    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.

    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.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • dwain.c (5/24/2012)


    ...so what makes you think I could do so here?

    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.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (5/24/2012)


    dwain.c (5/24/2012)


    ...so what makes you think I could do so here?

    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.

    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).


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • dwain.c (5/24/2012)


    Jeff Moden (5/24/2012)


    dwain.c (5/24/2012)


    ...so what makes you think I could do so here?

    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.

    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).

    I'm recursively challenged, as well. I've been married more than once. 😛

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (5/24/2012)


    dwain.c (5/24/2012)


    Jeff Moden (10/24/2010)


    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.

    Oh my! No fair! I love posting recursive CTE solutions and you beat me to it.

    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.

    Jeff, could you post this as a challenge, along with a test set? I'd love to take a whack at it.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • You mean just as a separate post or here or did you have something else in mind, Barry?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (5/26/2012)


    You mean just as a separate post or here or did you have something else in mind, Barry?

    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...

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • RBarryYoung (5/26/2012)


    Jeff Moden (5/26/2012)


    You mean just as a separate post or here or did you have something else in mind, Barry?

    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...

    Got it. I'll see what i can do. Thanks, Barry.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 18 total)

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