I have a bill of material list from a table, which I will try to convert over to a different BOM table. The list I have is based on a column with materials and levels. But I need to create a list of materials and components. I have illustrated below how the list looks and how I want to convert it. There can be up to 10 levels. But this is too tricky for me. Someone who can help me?
Exampel how it looks like today:
ID Material SLEVEL QTY
-----------------------------------------------------------
1 Material123 0 1
2 9515463A 1 1
3 1734279 2 1
4 3201463 2 4
5 9515463B 1 1
6 1734163 2 1
7 3201463 2 4
9 8349834 2 2
10 1349833 3 2
11 1463918 3 4
12 Material456 0 1
13 9512070 1 1
14 1621405 2 1
15 1734534 2 2
16 1734535 2 1
17 9513294 1 1
18 1621750 2 3
I want to make a SQL that change the BOM list so it becomes like this:
Material Component Qty SLevel
-------------------------------------------------
Material123 9515463A 1 0
Material123 1734279 1 0
Material123 3201463 1 0
Material123 9515463B 1 0
Material123 1734163 1 0
Material123 3201463 1 0
Material123 8349834 1 0
Material123 1349833 1 0
Material123 1463918 1 0
9515463A 1734279 1 1
9515463A 3201463 4 1
9515463B 1734163 1 1
9515463B 3201463 4 1
9515463B 8349834 2 1
8349834 1349833 2 2
8349834 1463918 4 2
Material456 9512070 1 0
Material456 1621405 1 0
Material456 1734534 1 0
Material456 1734535 1 0
Material456 9513294 1 0
Material456 1621750 1 0
9512070 1621405 1 1
9512070 1734534 2 1
9512070 1734535 1 1
9513294 1621750 3 1
asked
16 Oct '15, 14:53
Rolle
558●49●51●61
accept rate:
0%
Do you have had a look at other related FAQs here (hint: search for "recursion") or the SA docs, say
Parts explosion problem?
I have checked this but unfortunately it becomes too complex for me to get to where this is not the same principle. I know it goes, but the question is how. Do you have any idea?
Well, to me it's not clear how the different parts/components are connected. Does the order of the entries matter here? (I usually would expect a parent/child relationship here for each entry.)
FWIW, if you have a question how to build an according SQL query, I strongly recommend that you try to tell about the business logic as much as possible. Just to show an input sample and a desired result is surely helpful but not sufficient to make it easy for others to help. And it would be really helpful if you would supply a sample script to build the original table and its data.
The main material always starts when Slevel is 0. The main material component are all subsequent slevel 1. Slevels 1 components are all subsequent slevel 2 and so on. This will run until slevel is 0 again and this will start again as above. There can be up to 10 levels per main material. ID field is just a counter. I hope I have clarified it a bit ...
It's still not clear to me: How does material 9515463A "know" that it belongs to main material Material123 and not to Material456 as both have Slevel = 0?
In my understanding the ID order seems relevant here but that would not look like a relational-based approach (i.e. there's no FK link between main parts and components) and you also tell that "ID field is just a counter".
Please note that rows in a table in a RDMS are unordered by design.
As stated, it would be helpful if you could supply a small script to create and fill the table with your sample data.