SQL Server 2012: How to get up to 5 hierarchy levels of data from original to derived product table

Question

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. I hope this is a clear explanation.

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.

This is a link to get a copy of table data:

https://drive.google.com/open?id=1hP7kRQsl_8YzEu4dK9Z8J91y-2Gvh7Qi

Please advice. Many thanks.

UPDATE

I see that sku P55645 was used to create 9 derived products, and in this case none of the derived products became a parent. That is totally ok.
I just think that it might be easier to read if rather than display 9 rows, each one of the skus gets stuffed in a single line, starting with the top level sku.

As it currently is:
P0055645    P0098245    P0055645, P0098245
P0055645    P0110959    P0055645, P0110959
P0055645    P0110960    P0055645, P0110960
P0055645    P0110961    P0055645, P0110961
P0055645    P0110962    P0055645, P0110962
P0055645    P0110963    P0055645, P0110963
P0055645    P0110964    P0055645, P0110964
P0055645    P0110965    P0055645, P0110965
P0055645    P0157714    P0055645, P0157714

I think this might read better:

P0055645, P0098245, P0110959, P0110960, P0110961, P0110962, P0110963, P0110964, P0110965, P0157714

This is also current output that I am having a hard time following:

Parent      Derived     Hierarchy
P0172879    P0178192    P0172879, P0178192
P0178192    P0178206    P0172879, P0178192, P0178206 - until this point i see that the relationship of parent child and then the chil becoming a parent is well illustrated

P0178206    P0178219    P0172879, P0178192, P0178206, P0178219 -- but then P0178206 becomes the parent of 6 new skus. Do you think this looks good, or should the output be like the first example I described?
P0178206    P0178220    P0172879, P0178192, P0178206, P0178220
P0178206    P0178221    P0172879, P0178192, P0178206, P0178221
P0178206    P0178222    P0172879, P0178192, P0178206, P0178222
P0178206    P0178223    P0172879, P0178192, P0178206, P0178223
P0178206    P0178224    P0172879, P0178192, P0178206, P0178224

SECOND UPDATE

P0170926    P0170928    P0170926, P0170928 -- if sku load on form is P0170926, then the query will load a record-set of 2 records
P0170928    P0170929    P0170926, P0170928, P0170929 -- if user loads on form P0170928 then it will load 3 records including P0170926

P0170932    P0174069    P0170932, P0174069  -- here if user loads P0170932 the record set will only contain one record, shouldn't all the records up to P0174075 be also included?
P0170932    P0174070    P0170932, P0174070
P0170932    P0174071    P0170932, P0174071
P0170932    P0174072    P0170932, P0174072
P0170932    P0174073    P0170932, P0174073
P0170932    P0174074    P0170932, P0174074
P0170932    P0174075    P0170932, P0174075

How do you want to be able to read the data? Could you share an example output? — Chris Mack, Dec 30 '17 at 00:20
Hi Chris. Sorry I had to drive and just made it home. I don’t have a clear idea of how to format the output yet. I think I just want to be able to indicate the relationship tree/ breakdown I’m a say that a non technical user can easily tell what is the top parent sku and what in which order the children became children and parents where it fits. — erasmo carlos, Dec 30 '17 at 00:56
Hi Chris, II thought about the output and looked at the data and I have a couple of ideas I would like to share with you and see if you can help me with the format. I am going to update my question and include the sample output in there. Thank you. — erasmo carlos, Jan 02 '18 at 16:54
I did it. Right after where it reads UPDATE. I added notes to the right of the columns to explain. Please let me know if it makes sense. Thank you — erasmo carlos, Jan 02 '18 at 17:10

Chris Mack · Accepted Answer · 2018-01-03T18:08:35.917

Something like this (adapted from this answer):

WITH Skus as
(
    SELECT
        Sku_Parent Sku
        , CAST(NULL AS varchar(10)) Parent
        , CAST(Sku_Parent AS varchar(max)) Hierarchy
    FROM YourTable P
    WHERE Sku_Parent NOT IN (SELECT Sku_Child FROM YourTable)

    UNION ALL

    SELECT
        P.Sku_Child
        , P.Sku_Parent
        , M.Hierarchy + ', ' + CAST(P.Sku_Child AS varchar(max))
    FROM
        YourTable P 
        JOIN Skus M ON M.Sku = P.Sku_Parent
)

SELECT DISTINCT
    Sku
    , Parent
    , Hierarchy
FROM Skus

Table schema:

Adapted to your table schema:

WITH [Skus]
AS
(
    SELECT
        [P].[SKU_original] [Sku]
        , CAST(NULL AS VARCHAR(12)) [Parent]
        , CAST([P].[SKU_original] AS VARCHAR(MAX)) [Hierarchy]
    FROM [dbo].[production_derived_products] [P]
    WHERE [P].[SKU_original] NOT IN
        (
            SELECT [SKU_derived]
            FROM [dbo].[production_derived_products]
        )

    UNION ALL

    SELECT
        [P].[SKU_derived]
        , [P].[SKU_original]
        , [M].[Hierarchy] + ', ' + CAST([P].[SKU_derived] AS VARCHAR(MAX))
    FROM
        [dbo].[production_derived_products] [P]
        JOIN [Skus] [M] ON [M].[Sku] = [P].[SKU_original]
)

SELECT DISTINCT
    [Skus].[Sku]
    , [Skus].[Parent]
    , [Skus].[Hierarchy]
FROM [Skus]
;

UPDATE

With regards to this:

P0170932    P0174069    P0170932, P0174069  -- here if user loads P0170932 the record set will only contain one record, shouldn't all the records up to P0174075 be also included?
P0170932    P0174070    P0170932, P0174070
P0170932    P0174071    P0170932, P0174071
P0170932    P0174072    P0170932, P0174072
P0170932    P0174073    P0170932, P0174073
P0170932    P0174074    P0170932, P0174074
P0170932    P0174075    P0170932, P0174075

The reason it won't load one list of all of the records is because that column is for the hierarchy, i.e. the output you suggested would describe P0174070 as a child of P0174069, when it's actually a child of P0170932.

Anyway, I've played around with this a lot, and I think the following will give you just about anything you would need (let me know if not).

I've used a WHERE clause at the end, so you can run this and get a quick demo, but you can remove that to see everything.

WITH Skus as
(
    SELECT
        SKU_original Sku
        , CAST(NULL AS varchar(12)) Parent
        , SKU_original AbsoluteHierarchicalParent
        , CAST(SKU_original AS varchar(max)) Hierarchy
        , 0 [Level]
    FROM production_derived_products P
    WHERE SKU_original NOT IN (SELECT SKU_derived FROM production_derived_products)

    UNION ALL

    SELECT
        P.SKU_derived
        , P.SKU_original
        , M.AbsoluteHierarchicalParent
        , M.Hierarchy + ', ' + CAST(P.SKU_derived AS varchar(max))
        , M.[Level] + 1
    FROM
        production_derived_products P 
        JOIN Skus M ON M.Sku = P.SKU_original
)
,

SkusDistinct
AS
(
    SELECT DISTINCT
        Sku
        , Parent
        , AbsoluteHierarchicalParent
        , Hierarchy
        , [Level]
    FROM Skus
)
,

SkuHierarchies
AS
(
    SELECT
        Sku
        , Sku StartSku
        , 0 [Level]
    FROM
        (
            SELECT SKU_original Sku
            FROM production_derived_products

            UNION

            SELECT SKU_derived 
            FROM production_derived_products
        ) AllSkus

    UNION ALL

    SELECT
        P.SKU_derived
        , M.StartSku
        , M.[Level] + 1
    FROM
        production_derived_products P 
        JOIN SkuHierarchies M ON M.Sku = P.SKU_original
)
,

SkuHierarchiesDistinct
AS
(
    SELECT DISTINCT
        Sku
        , StartSku
        , [Level]
    FROM SkuHierarchies
)

SELECT
    SD1.Sku
    , SD1.Parent
    , SD1.AbsoluteHierarchicalParent
    ,
        STUFF(
            (
                SELECT ', ' + SD2.Sku
                FROM SkusDistinct SD2
                WHERE SD2.Parent = SD1.Sku
                ORDER BY SD2.Sku
                FOR XML PATH ('')
            )
        , 1, 2, '') ImmediateChildren
    , SD1.Hierarchy Heritage
    , SD1.[Level]
    ,
        STUFF(
            (
                SELECT ', ' + H.Sku + ' (' + CAST(H.[Level] AS varchar) + ')'
                FROM SkuHierarchiesDistinct H
                WHERE H.StartSku = SD1.Sku
                ORDER BY
                    H.[Level]
                    , H.Sku
                FOR XML PATH ('')
            )
        , 1, 2, '') SkuHierarchyByLevel
    , (SELECT MAX([Level]) + 1 FROM SkuHierarchiesDistinct WHERE StartSku = SD1.Sku) SkuHierarchyTotalLevels
    ,
        STUFF(
        (
            SELECT ', ' + SD2.Sku + ' (' + CAST(SD2.[Level] AS varchar) + ')'
            FROM SkusDistinct SD2
            WHERE SD2.AbsoluteHierarchicalParent = SD1.AbsoluteHierarchicalParent
            ORDER BY
                SD2.[Level]
                , SD2.Sku
            FOR XML PATH ('')
        )
    , 1, 2, '') FullHierarchyByLevel
    , MAX(SD1.[Level]) OVER (PARTITION BY AbsoluteHierarchicalParent) + 1 FullHierarchyTotalLevels
FROM SkusDistinct SD1
WHERE AbsoluteHierarchicalParent = 'P0172879'

I am trying to implement your proposed query, but I get an error message: Types don't match between the anchor and the recursive part in column "Parent" of recursive query "Skus". — erasmo carlos, Dec 29 '17 at 23:05
What's the error? Note that you'll most likely need to change `YourTable` to whatever your table is. — Chris Mack, Dec 29 '17 at 23:05
Hmm, is your `Sku_Parent` column definitely `varchar(10)` or was that something you used for the example? — Chris Mack, Dec 29 '17 at 23:08
Yes I did the pertinent changes to adapt it to my context. Thank you. — erasmo carlos, Dec 29 '17 at 23:08
I reviewed your code. You'll need to `CAST` the `NULL` as whatever type `SKU_original` is. Is it definitely `varchar(10)`? — Chris Mack, Dec 29 '17 at 23:09
Ok, I checked your schema. You'll need to replace `varchar(10)` with `varchar(12)`. — Chris Mack, Dec 29 '17 at 23:11
it is working now, but I am not sure how to read the data now. the table has a total of 7047 rows, but your example query returns 45724 — erasmo carlos, Dec 29 '17 at 23:24
I can upload a file with the table data if that makes it easier. — erasmo carlos, Dec 29 '17 at 23:24
There's a chance it's a lot of records without parents, If you try adding `WHERE Parent IS NOT NULL` that might give you the expected number? (Your example data had 8 rows but returns 9 because of `N0179890`.) — Chris Mack, Dec 29 '17 at 23:28
Thank you Chris. As soon as I get home I will try your code, and report back. — erasmo carlos, Jan 03 '18 at 02:51
Hi Chris, I tried your code. Still trying to understand it well. I have a question about the field that would make sense to use on the form, when a search is performed, is it "AbsoluteHierarchicalParent"? — erasmo carlos, Jan 03 '18 at 17:02
It would depend on what you're looking for, `AbsoluteHierarchicalParent` will give you multiple rows, one for each `Sku` in that family tree (`AbsoluteHierarchicalParent` is basically the top of the tree). Using `Sku` would give you all of the data for that particular `Sku` (one row), including the full family tree, immediate children, parent, `AbsoluteHierarchicalParent`, etc. So there's a few different ways you could use it. — Chris Mack, Jan 03 '18 at 17:11
Yes, I want to enter a sku and then return all of its children and in the case that sku also has a parent, include that on the list. Perhaps this is already included in one of the columns your query is returning? — erasmo carlos, Jan 03 '18 at 17:26
By the way Chris, the work you put on your query is awesome. Thank you so much. — erasmo carlos, Jan 03 '18 at 18:02
No problem! I've added an extra field into the query, as, yeah, you seem to need it the other way around. So now there is a "Full Hierarchy", and also a "Sku Hierarchy", as unless you select a top level Sku, you can't see which of the children belong to the selected Sku in the `FullHierarchyByLevel` field. You can in the `SkuHierarchyByLevel` field, which begins at level 0 for each Sku. — Chris Mack, Jan 03 '18 at 18:10
I see the field FullHierarchyByLevel, but not SkuHierarchyByLevel. — erasmo carlos, Jan 03 '18 at 18:48
Take a look at my answer again, it's there, 17th line from the bottom. :) — Chris Mack, Jan 03 '18 at 18:57
you are correct, I was looking at the previous query. I got it now. Thank you! Let me run it and compare some of the expected results and will get back to you as soon as I am done with that. Again, many thanks. — erasmo carlos, Jan 03 '18 at 19:06

SQL Server 2012: How to get up to 5 hierarchy levels of data from original to derived product table

1 Answers1