December 29, 2017 at 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.
December 30, 2017 at 3:27 am
I can't paste links from the phone, a "feature" of SSC, but have a Google of "Hierarchies on Steroids". The first two results are the 2 part article written by Jeff Modern on this site.
Have a look and see how far you get with that, and if you struggle then please post back with what you've tried and where you got stuck. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 2, 2018 at 10:26 am
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
January 3, 2018 at 8:47 pm
I came from a mfg background, and dealt with multi level BOM's.
A parent would be listed as level 1, children level 2.
Level 2 children could themselves be a parent, with a level 3 list of components.
From your description, you mention 6 levels, although ours varied and went deeper.
We were going from a mfg parent, and then blowing through to all the purchased components and qtys that it took to build any parent item.
I think what you are looking for is a CTE to make a distinct list of parents, and then chase down the hierarchy.
And then create more of an indented BOM look for the results.
Parent A
Child A
Child B
Child 1 of B
Child 2 of B
Your lists of results seem a bit confusing to me, as Parents are being repeated in what should be all the Child Items only.
So you may want to add a better example of what you really want to see in the end.
Especially where in my example, Child B is a Parent in it's own right, but also consumed as a sub assembly when Parent A is made.
Your requirements likely differ from ours, as we also could have a sub assembly qty of more than 1 per parent, so a bit of math might be in order.
Hope this helps.
January 4, 2018 at 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.
January 4, 2018 at 5:50 am
Using your original table definition I created a temp table with 7047 rows and set to work.
Firstly, test out the 5-levels restriction. The easy way to do this is using left-joins to a CTE of the original table, of rows where the parent SKU isn't a child SKU anywhere in the table. I stopped at 19 levels...;WITH TopLevelParents AS (
SELECT *
FROM #Temp p1 -- (7047 rows affected)
WHERE NOT EXISTS (SELECT 1 FROM #Temp p2 WHERE p2.Sku_Child = p1.Sku_Parent)
-- (6113 rows affected)
)
SELECT t.ID, t.Sku_Parent,
c1.Sku_Parent, c2.Sku_Parent, c3.Sku_Parent, c4.Sku_Parent, c5.Sku_Parent, c6.Sku_Parent, c7.Sku_Parent, c8.Sku_Parent, c9.Sku_Parent, c10.Sku_Parent,
c11.Sku_Parent, c12.Sku_Parent, c13.Sku_Parent, c14.Sku_Parent, c15.Sku_Parent, c16.Sku_Parent, c17.Sku_Parent, c18.Sku_Parent, c19.Sku_Parent
FROM TopLevelParents t
LEFT JOIN #Temp c1 ON c1.Sku_Parent = t.Sku_Child
LEFT JOIN #Temp c2 ON c2.Sku_Parent = c1.Sku_Child
LEFT JOIN #Temp c3 ON c3.Sku_Parent = c2.Sku_Child
LEFT JOIN #Temp c4 ON c4.Sku_Parent = c3.Sku_Child
LEFT JOIN #Temp c5 ON c5.Sku_Parent = c4.Sku_Child
LEFT JOIN #Temp c6 ON c6.Sku_Parent = c5.Sku_Child
LEFT JOIN #Temp c7 ON c7.Sku_Parent = c6.Sku_Child
LEFT JOIN #Temp c8 ON c8.Sku_Parent = c7.Sku_Child
LEFT JOIN #Temp c9 ON c9.Sku_Parent = c8.Sku_Child
LEFT JOIN #Temp c10 ON c10.Sku_Parent = c9.Sku_Child
LEFT JOIN #Temp c11 ON c11.Sku_Parent = c10.Sku_Child
LEFT JOIN #Temp c12 ON c12.Sku_Parent = c11.Sku_Child
LEFT JOIN #Temp c13 ON c13.Sku_Parent = c12.Sku_Child
LEFT JOIN #Temp c14 ON c14.Sku_Parent = c13.Sku_Child
LEFT JOIN #Temp c15 ON c15.Sku_Parent = c14.Sku_Child
LEFT JOIN #Temp c16 ON c16.Sku_Parent = c15.Sku_Child
LEFT JOIN #Temp c17 ON c17.Sku_Parent = c16.Sku_Child
LEFT JOIN #Temp c18 ON c18.Sku_Parent = c17.Sku_Child
LEFT JOIN #Temp c19 ON c19.Sku_Parent = c18.Sku_Child
ORDER BY c19.ID desc, c18.ID desc, c17.ID desc, c16.ID desc, c15.ID desc, c14.ID desc, c13.ID desc, c12.ID desc, c11.ID desc,
c10.ID desc, c9.ID desc, c8.ID desc, c7.ID desc, c6.ID desc, c5.ID desc, c4.ID desc, c3.ID desc, c2.ID desc, c1.ID desc, t.ID desc
Now, you could arbitrarily stop at 5 levels, or you may wish to resolve all the way through.
Having made that decision, I agree with Greg - it's up to you to decide how you want the results to be returned and represented. Post back what you decide and folks will help.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 4, 2018 at 5:51 am
aaron.reese - Thursday, January 4, 2018 4:26 AMGoogle 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 +1If 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.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 4, 2018 at 6:41 am
ChrisM@Work - Thursday, January 4, 2018 5:51 AMaaron.reese - Thursday, January 4, 2018 4:26 AMGoogle 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 +1If 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.
January 4, 2018 at 7:31 am
Greg Edwards-268690 - Thursday, January 4, 2018 6:41 AMChrisM@Work - Thursday, January 4, 2018 5:51 AMaaron.reese - Thursday, January 4, 2018 4:26 AMGoogle 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 +1If 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.
Of course - but I'd describe this as display, or at least getting the result set out in a form which won't take too much effort to display.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 4, 2018 at 8:19 am
itortu - Friday, December 29, 2017 3:57 PMI 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 N0180334I 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
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply