Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

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

Kevin CrossChief Technology Officer
CERTIFIED EXPERT
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.

Before we begin, here is a brief table of contents for your convenience:
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

Open in new window

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
                      );

Open in new window

[/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
                      ;

Open in new window

[/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

Open in new window



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

Open in new window

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
                         ;

Open in new window

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

Open in new window

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
                      ;

Open in new window


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

Open in new window

(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
                         )

Open in new window

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

Open in new window


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

Open in new window


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
                      ;

Open in new window


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;
                      
                      -- add current level
                      insert into #mlBOM
                      select @CurrentLevel, ParentPart, ParentPart, ComponentPart
                           , QuantityPer, QuantityPer
                      from BOM
                      where ParentPart = 'Car'
                      ;
                      
                      -- iteratively add secondary levels
                      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;

Open in new window


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.

If you didn't or otherwise feel the need to vote No, please first leave a comment to give me a chance to answer and perhaps to improve this article.

Thank you!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
5
15,349 Views
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Father, husband and general problem solver who loves coding SQL, C#, Salesforce Apex or whatever.

Comments (4)

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

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

Author

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
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Author

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

Open in new window

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.