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.

  • Hi Thom A.
    This is a query that I am currently using and it does return accurate data for what I can tell so far, but the formatted output is still a bit hard to read. I was wondering if I might be able to get some help tweaking this part.


    ;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].[Parent]  
      , [Skus].[Sku] [Derived]
      , [Skus].[Hierarchy]
      FROM
       [Skus]
      WHERE [Skus].[Parent] IS NOT NULL
      ORDER BY [Skus].[Parent],[Skus].[Sku];
    ??

    In the sample data located right below this, 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.


    As it currently is:

    Parent  Derived Hierarchy
    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 for this case the output might read better if each one of the children skus gets stuffed in a single line, starting with the top level sku:

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

    Along with the same current output data, I am having a hard time following this story:

    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