Convert a bill of material list with levels

[former_SQLA_member1694869]

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

[VolkerBarth]

Do you have had a look at other related FAQs here (hint: search for "recursion") or the SA docs, say

Parts explosion problem?

[former_SQLA_member1694869]

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?

[VolkerBarth]

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.

[former_SQLA_member1694869]

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 ...

[VolkerBarth]

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.