Editor's Choice: This article has been selected by our editors as an exceptional contribution.

# (MS SQL 2005+) T-SQL Techniques: Logical BOM Explosion

Father, husband and general problem solver who loves coding SQL, C#, Salesforce Apex or whatever.
Published:
Updated:
"How to: Create multi-level BOM from single-level BOMs: MS/SQL", a question here at Experts-Exchange inspired me to write an article on expanding or "exploding" bills of materials (BOMs) and other such hierarchical data about five months ago; however, I found an existing article by Experts-Exchange's own mbizup entitled " Expanding a Hierarchical Data Structure", which is such a good coverage of the subject that I will not go as in depth here.  Therefore, please understand this article to be a simple tips & tricks look at handy technique(s) in MS SQL Server 2005 or higher for handling the recursion discussed by mbizup, but will recommend to all a thorough reading of that article if interested in the topic overall.  For those of you on SQL 2000 or lower, I will show the iterative approach for comparison; therefore, if you are in that environment and seeking a solution you may still find it here.

Introduction: The problem.
Recursive Solution:
1. Functional recursion.
2. Self-referencing common table expression (CTE).
Iterative Solution
Summary
References

## Introduction:

The problem.

The table structure and sample data in the EE question (ID: 24699504) referenced above is very typical of how I have seen most systems handle the parent-child relationship / storage of BOMs.

So the situation starts with a table called BOM (for example) with the following data:
``````ParentPart     ComponentPart     QuantityPer
----------     -------------     -----------
Body           Frame                  1
Body           Hood/Trunk             2
Body           Roof                   1
Car            Body                   1
Frame          Door                   4
Car            Engine                 1
Door           Window                 2
Window         Glass                 100
Roof           Window                 1
``````
ParentPart - varchar representing an item or entity that is constructed from other items.
ComponentPart - varchar representing a specific item used to make a given parent part.
QuantityPer - integer or decimal value representing the quantity per assembly or ratio of component to parent parts.

[step=""]«« DDL to create BOM table.»»
``````create table bom(
ParentPart varchar(100),
ComponentPart varchar(100),
QuantityPer int
);
``````
[/step]
[step=""]«« SQL to insert sample data into BOM table.»»
``````insert into bom(
ParentPart,
ComponentPart,
QuantityPer
) select 'Car', 'Body', 1
union select 'Car', 'Engine', 1
union select 'Frame', 'Door', 4
union select 'Body', 'Frame', 1
union select 'Body', 'Hood/Trunk', 2
union select 'Body', 'Roof', 1
union select 'Door', 'Window', 2
union select 'Window', 'Glass', 100
union select 'Roof', 'Window', 1
;
``````
[/step]

And the requirement is to expand this bill of material for a given assembly part so that you get a true reflection of how many of each component part is actually required in the fabrication process.  This can be done for all parent parts, but we will stay with the scenario as posed in the originating question and explode our BOM for assemblies that are not also sub-assemblies of other items (i.e., finished goods), making our desired end result:
``````FinishedGood     ParentPart     ComponentPart     QuantityPer     ExtendedQuantityPer
------------     ----------     -------------     -----------     -------------------
Car              Car            Body                   1                   1
Car              Car            Engine                 1                   1
Car              Body           Frame                  1                   1
Car              Body           Hood/Trunk             2                   2
Car              Body           Roof                   1                   1
Car              Frame          Door                   4                   4
Car              Door           Window                 2                   8
Car              Roof           Window                 1                   1
Car              Window         Glass                 100                 900
``````

## Recursive Solution

Using Microsoft's SQL Server 2005 or higher, this can be solved nicely using WITH keyword and recursion to blow through the BOM hierarchy, but keep in mind that there are always multiple ways to solve a problem.  However, I liked this approach because I had previously chosen recursion through procedural or functional recursion which is when you define a method that subsequently calls itself as part of its implementation.  A nice example of this is a factorial function since to get f(n) you need to know f(n-1) and so on, which is illustrated quite nicely at en.wikipedia.org/wiki/Recursion.

Here are these two different recursive approaches with the functional one first.

## 1.

Functional recursion.

As implied, this solution involves a function.  So to begin down this path, we will create a user defined function (UDF) like fn_GetMultiLevelBOM which takes a top-level part number as a parameter and returns a table variable that will contain our desired information.
``````create function [dbo].[fn_GetMultiLevelBOM]
(
-- Input parameters
@FinishedGood varchar(100)
)
returns
@BOM table
(
-- Returned table layout
FinishedGood varchar(100),
ParentPart varchar(100),
ComponentPart varchar(100),
QuantityPer int,
ExtendedQuantityPer int
)
as
begin
-- Function body below goes here
return
end
``````
Note here that "finished good" is used to identify to parent part passed, but this could be a sub-assembly (i.e., a part that is a parent but also a component of a higher-level part).

So we have satisfied the "functional" part, now for the "recursive" portion by utilizing a piece of code like the below that inserts the component parts that belong to the single-level BOM of the passed parent part and then recursively calling itself to insert the BOM of these components.
``````   -- add current level
insert into @BOM
select ParentPart, ParentPart, ComponentPart, QuantityPer, QuantityPer
from BOM
where ParentPart = @FinishedGood
;

-- explode downward
insert into @BOM
select c.FinishedGood, n.ParentPart, n.ComponentPart, n.QuantityPer
, n.ExtendedQuantityPer * c.QuantityPer
from @BOM c
cross apply dbo.fn_GetMultiLevelBOM(c.ComponentPart) n
;
``````
This will result in:
``````FinishedGood     ParentPart     ComponentPart     QuantityPer     ExtendedQuantityPer
------------     ----------     -------------     -----------     -------------------
Car              Car            Body                   1                   1
Car              Car            Engine                 1                   1
Car              Body           Frame                  1                   1
Car              Body           Hood/Trunk             2                   2
Car              Body           Roof                   1                   1
Car              Frame          Door                   4                   4
Car              Door           Window                 2                   8
Car              Roof           Window                 1                   1
Car              Window         Glass                 100                 800
Car              Window         Glass                 100                 100
``````
Note that Window - Glass combination appears twice since it is on the BOM of two different components Roof and Door.

So a final query could look like this:
``````select FinishedGood, ParentPart, ComponentPart
, QuantityPer, sum(ExtendedQuantityPer) as ExtendedQuantityPer
from dbo.fn_GetMultiLevelBOM('Car')
group by FinishedGood, ParentPart, ComponentPart, QuantityPer
;
``````

Barring any issues with how I translated this function for this article, the real-world version worked well in the past; however, as eluded to earlier, CTEs make this easier.

## 2.

Self-referencing common table expression (CTE).

As with the previous recursive method, the first step here is to establish the single-level BOM of the top-level item(s).
``````   select ParentPart as FinishedGood, ParentPart, ComponentPart
, QuantityPer, QuantityPer as ExtendedQuantityPer
from BOM
where ParentPart = @FinishedGood
``````
(or as shown in the question)
``````   select ParentPart as FinishedGood, ParentPart, ComponentPart
, QuantityPer, QuantityPer as ExtendedQuantityPer
from BOM
where not exists (
select 1
from BOM as lookup
where lookup.ComponentPart = BOM.ParentPart
)
``````
Both of these in our simple example, gets us the following:
``````FinishedGood     ParentPart     ComponentPart     QuantityPer     ExtendedQuantityPer
------------     ----------     -------------     -----------     -------------------
Car              Car            Body                   1                   1
Car              Car            Engine                 1                   1
``````

Now, the second step would again be the same: the recursive piece, which will grab the records above and get their bill of material.  Something like this:
``````   select c.FinishedGood, n.ParentPart, n.ComponentPart
, n.QuantityPer, n.QuantityPer * c.ExtendedQuantityPer
from BOM n
inner join {previous query} c on c.ComponentPart = n.ParentPart
``````

So as you can see, we haven't changed methodology really, but consider that the above second step would have to be repeated until there were no longer any child components (i.e., you reach parts with no BOM of their own) and take a look at how CTEs handles this for you with the simple inclusion of the UNION ALL keyword(s) and self-referencing of the CTE in place of "{previous query}".
``````;with mlBOM
as
(
select ParentPart as FinishedGood, ParentPart, ComponentPart
, QuantityPer, QuantityPer as ExtendedQuantityPer
from BOM
where ParentPart = 'Car'

union all -- CTE recursion

select c.FinishedGood, n.ParentPart, n.ComponentPart
, n.QuantityPer, n.QuantityPer * c.ExtendedQuantityPer
from BOM n
inner join mlBOM c on c.ComponentPart = n.ParentPart
)
-- final select aggregating ExtendedQuantityPer values
-- displays unique ParentPart - ComponentPart combinations
select FinishedGood, ParentPart, ComponentPart, QuantityPer
, sum(ExtendedQuantityPer) as ExtendedQtyPer
from mlBOM
group by FinishedGood, ParentPart, ComponentPart, QuantityPer
;
``````

This takes advantage of the CTE's properties as a virtual table.  As such, the rows added at each level of the BOM themselves become subject to the check for child components.  The key when using this approach is to ensure that your second query has a limiting factor that makes the recursion finite by eventually resulting in an empty recordset.  In this example, the inner join on the condition of ComponentPart = ParentPart handles that naturally; however, if you don't have such a condition or, for example, only want X levels of the BOM, you can use the MAXRECURSION option explained in the SQL Server BOL.

## Iterative Solution

For those using older versions of T-SQL or those who simply have a curiosity on how this would look without recursion: the following code will show the same process of adding the first-level BOM to a temporary table and then using a while loop to add in the additional levels.  This version uses an index or counter to facilitate that we don't keep adding rows of components for the same level of the BOM over and over; therefore, in the final select if you want X levels displayed, you can use the BomLevel field as a filter.  To avoid an infinite loop here, the EXISTS clause is used to control the conclusion of the iteration process.
``````create table #mlBOM(
BomLevel int,
FinishedGood varchar(100),
ParentPart varchar(100),
ComponentPart varchar(100),
QuantityPer int,
ExtendedQuantityPer int
);

-- counter variable
declare @CurrentLevel int;
set @CurrentLevel = 0;

insert into #mlBOM
select @CurrentLevel, ParentPart, ParentPart, ComponentPart
, QuantityPer, QuantityPer
from BOM
where ParentPart = 'Car'
;

while exists(
select 1
from BOM n
inner join #mlBOM c
on c.ComponentPart = n.ParentPart
and c.BomLevel = @CurrentLevel
)
begin
insert into #mlBOM
select c.BomLevel+1, c.FinishedGood, n.ParentPart, n.ComponentPart
, n.QuantityPer, n.QuantityPer * c.ExtendedQuantityPer
from BOM n
inner join #mlBOM c on c.ComponentPart = n.ParentPart
and c.BomLevel = @CurrentLevel
;

-- increment counter
set @CurrentLevel = @CurrentLevel + 1;
end

select FinishedGood, ParentPart, ComponentPart, QuantityPer
, sum(ExtendedQuantityPer) as ExtendedQtyPer
from #mlBOM
group by FinishedGood, ParentPart, ComponentPart, QuantityPer
;

drop table #mlBOM;
``````

Just a little more involved, but it works; therefore, hopefully it helps those who can't take advantage of other techniques shown.

## Summary

Good thing, I didn't go too in depth here ! *laughing* But, seriously, thank you reading and I hope you enjoyed learning how the introduction of the WITH common table expressions in SQL Server 2005 comes in handy for expanding hierarchical data such as bills of materials.  There were some other syntax tricks here, so hopefully there was something for everyone.

Thanks again!

Best regards and happy coding,

Kevin (aka MWVisa1)

## References

How to: Create multi-level BOM from single-level BOMs: MS/SQL
https://www.experts-exchange.com/questions/24699504/How-to-Create-multi-level-BOM-from-single-level-BOMs-MS-SQL.html

Expanding a Hierarchical Data Structure
https://www.experts-exchange.com/A_19.html

WITH common_table_expression (BOL)
http://msdn.microsoft.com/en-us/library/ms175972%28SQL.90%29.aspx

=-=-=-=-=-=-=-=-=-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=
If you found this article helpful, please click the Yes button after the question just below. This will give me (the author) a few points and might encourage me to write more articles.

Thank you!
=-=-=-=-=-=-=-=-=-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=
5
14,889 Views
Father, husband and general problem solver who loves coding SQL, C#, Salesforce Apex or whatever.

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Voted 'Yes!' above.
Chief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
Thank you, mbizup!

Your article was definitely instrumental in how this one shaped up and gives readers a good understanding IMHO of real-world BOM and other hierarchical data structures this technique could apply to.

Glad you liked this and thanks again.

Regards,
Kevin
Chief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
Since we are on the topic of recursion and given my motto of keep smiling in addition to the learning, here is a fun example of "the perils of recursion" http://languagelog.ldc.upenn.edu/nll/?p=2110.

--Kevin

Commented:
How would one add a column that displays the level in the tree to this query?

``````FinishedGood     ParentPart     ComponentPart     QuantityPer     ExtendedQuantityPer     Level
------------     ----------     -------------     -----------     -------------------  ----------
Car              Car            Body                   1                   1             1
Car              Car            Engine                 1                   1             1
Car              Body           Frame                  1                   1             2
Car              Body           Hood/Trunk             2                   2             2
Car              Body           Roof                   1                   1             2
Car              Frame          Door                   4                   4             3
Car              Door           Window                 2                   8             4
Car              Roof           Window                 1                   1             3
Car              Window         Glass                 100                 800            5
Car              Window         Glass                 100                 100            4
``````