<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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

Published on
22,865 Points
11,865 Views
5 Endorsements
Last Modified:
Awarded
Community Pick
Kevin Cross
Father, husband and general problem solver who loves coding SQL, C#, Salesforce Apex or whatever.
"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
http://www.experts-exchange.com/Q_24699504.html

Expanding a Hierarchical Data Structure
http://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
Comment
Author:Kevin Cross
  • 2
3 Comments
LVL 61

Expert Comment

by:mbizup
Voted 'Yes!' above.
0
LVL 61

Author Comment

by:Kevin Cross
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
0
LVL 61

Author Comment

by:Kevin Cross
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
0

Featured Post

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Join & Write a Comment

Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month