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