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.

  • January 4, 2018 at 8:19 am

    #1974476

    itortu - Friday, December 29, 2017 3:57 PM

    I have a table with 3 columns:

    Id (identity),Sku_Parent (varchar(10)),Sku_Child (varchar(10))

    I would like to get help to write a recursive query to obtain up to 5 parent-child relationship levels.

    When a newly derived product gets inserted, the new derived product also (eventually) can be used as a parent.
    I want to be able to track the history of each new derived product, 5 levels down and find the top parent. 

    Here is a sample data that currently resides in my table:


    7038    N0179890    N0180323
    7039    N0180323    N0180328
    7040    N0180323    N0180329
    7041    N0180323    N0180330
    7042    N0180323    N0180331
    7043    N0180323    N0180332
    7044    N0180323    N0180333
    7045    N0180323    N0180334

    I have found examples, but those I found assume that there is only one parent record and has a null value.

    Please advice. Many thanks.

    Can you explain what the "Original" and "Derived" columns in your spreadsheet represent?  I'm thinking that "Original" can loosely be interpreted as the "Parent" and "Derived" can be loosely interpreted as the "Child".

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