SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to get up to 5 hierarchy levels of data from original (parent) to derived (children) product...


How to get up to 5 hierarchy levels of data from original (parent) to derived (children) product table.

Author
Message
itortu
itortu
SSC Eights!
SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)

Group: General Forum Members
Points: 830 Visits: 241
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.
Attachments
derived_products.xlsx (19 views, 173.00 KB)
Thom A
Thom A
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46170 Visits: 15686
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. Smile


Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
itortu
itortu
SSC Eights!
SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)

Group: General Forum Members
Points: 830 Visits: 241
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

Greg Edwards-268690
Greg Edwards-268690
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10929 Visits: 8766
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.
aaron.reese
aaron.reese
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6463 Visits: 1028
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.
ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97720 Visits: 20711
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.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97720 Visits: 20711
aaron.reese - Thursday, January 4, 2018 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.


This is confusing - he's posted a recursive CTE as his sample code.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Greg Edwards-268690
Greg Edwards-268690
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10929 Visits: 8766
ChrisM@Work - Thursday, January 4, 2018 5:51 AM
aaron.reese - Thursday, January 4, 2018 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.


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.
ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97720 Visits: 20711
Greg Edwards-268690 - Thursday, January 4, 2018 6:41 AM
ChrisM@Work - Thursday, January 4, 2018 5:51 AM
aaron.reese - Thursday, January 4, 2018 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.


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.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)

Group: General Forum Members
Points: 512062 Visits: 44309
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search