After having been involved in the manufacturing industry for over five years, I have often been faced with recursive problems where I was to find the exploded quantities for a bill of materials (BOM). After my first investigations I use the recursive BOM CTE (common table expressions) that were introduced in MSSQL 2005. They worked, but they were extremely slow, and to create a kitting list for a number of different models using this method proved itself to be a poor way of doing things.
The solution to this was to process each level of the BOM and append each level to an output table.
The algorithm I used is:
set itemsfound = 0
set level = 0
get a list of parent parts as components to be exploded
set items found = number of parts to be appended to ouput table
while itemsfound > 0
append the list of components to the output table
increment the current level
<create a listof components>
select all the children in the structure table that have parents in the output table _
and where the level is equal to the current level - 1
multiply each childs requirement by the child's qty per and the parent's cumulative requirement
set items found = number of child components returned
repeat
For an example of this as a SQL implementation, please see How to explode a Bill of Materials using SQL