How to create a Loop that creates hierarchy levels from two columns

eliasskate_8 · June 17, 2019, 4:38pm

here is how my table is structured.

|BillNo | ComponentItemCode | Quatity PerBill |

ETC…

the table relationship is like this

I just what to return a table that like the following

|lvl 1 | lvl2 | lvl 3 |

I do not know how will be the best way to do this I was thinking of a loop but I do not know any idea any solution HELP PLEASE!!!

r937 · June 17, 2019, 4:47pm

that FROM clause is correct, but what is bom?? is that the actual table name?

eliasskate_8 · June 17, 2019, 4:54pm

When I assign the level and parent id I create a view from that query i forget to mention that. the view name is call bom

r937 · June 17, 2019, 6:10pm

why couldn’t you at least try the same FROM clause logic on the base table?

i would start with that, and then tie in the other tables

eliasskate_8 · June 17, 2019, 6:45pm

that is what I am trying to do but I have no idea How there is a function or I need to create a function or stored proc that why I asking for help because I do not know HOW

SamuelCalifornia · June 17, 2019, 7:29pm

Hierarchical data such as a BOM is complicated, perhaps the most complex type of data. I was employed for a major aircraft manufacturer and people would spell the name of guy responsible for the engineering parts list with a dollar sign in it. He claimed to get thousands of dollars per hour and I am sure he was very well compensated. Now we have the internet for finding answers but you need to spend time reading. It is not simple and cannot be made simple. The following are some relevant articles and you can find more.

One complication is the possibility of infinite recursion. It is especially complex. At the aircraft manufacturer the detection of infinite recursion was done using something they called tumble-down. The following are about infinite recursion.

I have written articles about writing code (not SQL) for retrieving hierarchical data hierarchically but I have been criticized for posting links to my website here. There is a copy in the Microsoft Technet website. Since you are using SQL I will assume you do not need any of that.

system · September 17, 2019, 2:29am

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.