Home Forums SQL Server 2012 SQL Server 2012 - T-SQL How to get up to 5 hierarchy levels of data from original (parent) to derived (children) product table. RE: How to get up to 5 hierarchy levels of data from original (parent) to derived (children) product table.

  • ChrisM@Work - Thursday, January 4, 2018 5:51 AM

    aaron.reese - Thursday, January 4, 2018 4:26 AM

    Google for Recursive CTE - basically a CTE that references itself.  You can set a level 0 in the CTE and then a derived level as the derived level of the parent +1

    If you want to present the hierarchy as a single row you will need to use STUFF..FOR XML and PIVOT()

    Have a go at some code and post back if you get stuck.

    This is confusing - he's posted a recursive CTE as his sample code.

    When you look at the results - with parent listed under itself for different children - it still needs work.
    At least from the way our business would want to see it.
    No indented BOM in the ERP would reflect this.

    Stopping at an arbitrary level might not hold up over time. Business requirements change over time.
    How the business expects to use this information may also influence how the results need to be presented, so if you haven't asked that of the business, be sure to ask.
    For example, they intend to query as a where used - what parents consume a specific derived unit.
    For us, since we went down to raw materials, we could audit a whole product family quickly.
    By pulling a whole family in, then presenting in a pivot table by size, see errors in qty called for.
    Or look for QOH for items that may be specific for a custom order not yet entered in the system.