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

here is how my table is structured.

|BillNo | ComponentItemCode | Quatity PerBill |

GK1935-WPS | GK1935-LLD-2-B | 2.0
GK1935-WPS | GK1935-RDD-2-A | 2.0
GK1935-WPS | WS-EXPAN | 1.0
GK1935-WPS | WRCBHSHO-WL | 1.0
GK1935-WPS | AC-12LD | 1.0
GK1935-WPS | AC-12UD | 1.0
GK1935-LLD-2-B | 02-060-00247 | 7.50
GK1935-LLD-2-B | 02-020-00028 | 2.0
GK1935-RRD-2-A | 02-060-00247 | 7.50
GK1935-RRS-2-A | 02-020-00028 | 2.0
WS-EXPAN | 02-040-00111 | 8.0
WS-EXPAN | 02-040-00112 | 8.0
WRCBHSHO-WL | 02-060-00202 | .8


the table relationship is like this


I just what to return a table that like the following

|lvl 1 | lvl2 | lvl 3 |

GK1935-WPS | GK1935-LLD-2-B | 02-060-00247
GK1935-WPS | GK1935-LLD-2-B | 02-060-00028
GK1935-WPS | GK1935-RDD-2-A | 02-020-00028
GK1935-WPS | GK1935-RDD-2-A | 02-020-00247
GK1935-WPS | WS-EXPAN | 02-040-00111
GK1935-WPS | WS-EXPAN | 02-040-00112
GK1935-WPS | WRCBHSHO-WL | 02-060-00202
GK1935-WPS | AC-12LD | 02-040-00107
GK1935-WPS | AC-12UD | 02-040-00107

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

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

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

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

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

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.

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