Home Forums Article Discussions Article Discussions by Author Discuss Content Posted by Jeff Moden Displaying Sorted Hierarchies (SQL Spackle) RE: Displaying Sorted Hierarchies (SQL Spackle)

  • March 10, 2011 at 7:07 am

    #1297074

    Jason-299789 (3/10/2011)

    Nice Article Jeff, and having spent a lot of time recently working with them its good to see alternative solutions.

    One common problem is people come across is when they have to flatten the hierarchy for data warehouses, eg Each level of the tree in a seperate column, and each row containing the entire tree for that Leaf node.

    In the example I gave, the hierarchical path column does contain the entire tree of ID's or names for that given Leaf node. So far as listing each level of the tree in a separate column goes, that would make for a whole lot of NULLs in many of the columns. I don't believe I'd use such a method because you have to change column names for similar queries on the structure. Instead, I'd recommend using a "Nested Set" hierarchy or the new preaggregated "warehouse" table method that I'll reveal when the larger article I'm writing on hierarchies comes out.

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