SQL Server Hierarchyid Example for Bill of Materials

By: Rick Dobson   |   Updated: 2019-07-23   |   Comments (2)   |   Related: > TSQL

Problem

We seek examples showing how to program bill of material applications with the SQL Server hierarchyid data type. Our database development team has recently been charged with upgrading the tracking and displaying of the assemblies and components used in making manufacturing goods for sale by our company. Our process for making finished goods is roughly like the AdventureWorks company so please use the AdventureWorks database in your demonstration examples.

Solution

A bill of materials is often referred to as a recipe for making finished goods. The AdventureWorks company manufactures three subcategories of bikes in ninety-seven specific models for sale to its customers. The bill of materials for each bike is comprised of a hierarchical list of assemblies and components. Each assembly is made up of a collection of components across a hierarchy of levels. The components from earlier levels become assemblies with new components until there are no new components at the next level. Each bike manufactured by the AdventureWorks company has its own series of assemblies comprised of components that specify a recipe for making the bike.

Microsoft introduced the hierarchyid data type to facilitate the designation and querying of hierarchical relations within SQL Server. However, it did not illustrate how to use this data type with the AdventureWorks bill of materials table. Instead, you are left to use the less rich capabilities of recursive CTEs. See this link for an illustration of this approach.

The hierarchyid data type is a standard for encoding and representing hierarchical relations with either of two equivalent formats -- hexadecimal characters or slash-delimited decimal values for denoting the position of nodes in a hierarchy. Within a bill of materials application, the nodes correspond to assemblies and components.

• The hexadecimal character format for representing hierarchical data type values points to bit streams. SQL Server uses these bit streams to internally denote hierarchyid values.
• The slash-delimited decimal values represent hierarchical data type values in a way that is easier for humans to read and interpret than bit streams. Numbers within successive slashes denote the position of nodes within a succession of hierarchical levels. The decimal values in the left-most slashes are for the top level of a hierarchy, and the decimal values within the right-most slashes are for the level most distant from the top of a hierarchy.

A prior tip (An Introduction to Representing and Querying Data with the Hierarchyid Data Type) presents the basics of creating and querying hierarchical data sources with the hierarchyid data type, but the tip did not focus on bill of material applications, which are widely used in manufacturing contexts. This tip leverages the prior tip introducing the hierarchyid data type with examples for creating and querying a bill of materials table with hierarchyid values. In particular, you will see examples on how to

• Distinguish between components and assemblies for manufacturing processes
• Track the transformation of components to assemblies over multiple hierarchical manufacturing levels and ultimately to finished goods
• Assign hierarchyid values to assemblies and components for a set of manufactured finished goods
• Validate the assignment of hierarchyid values to components for a set of manufactured finished goods
• Select an appropriate sort order for hierarchyid values that reflects the needs of your bill of materials application

Getting familiar with products, assemblies, and components for a bill of materials

The bill of material resources in the AdventureWorks database consists of a main table (BillOfMaterials) with support from two other tables (Product and UnitMeasure). Two additional tables (ProductCategories and ProductSubcategories) can also assist peripherally in bill of materials applications. These main and supporting tables reside in the Production schema. The BillOfMaterials table contains collections of hierarchically related rows for each finished good. Here’s a summary of columns within the BillOfMaterials table.

• The BillOfMaterialsID column is a column of identity values. The BillOfMaterialsID column serves as the table’s primary key.
• The ProductAssemblyID and the ComponentID columns from the BillOfMaterials table are both defined by foreign key relationships to the ProductID column in the Product table. The foreign key constraints require that ProductAssemblyID and ComponentID columns values must already exist in the Product table before they can exist in the BillOfMaterials table. Additionally, neither the ProductAssemblyID column nor the ComponentID column can contain null values.
  • The ComponentID column identifies a child in a parent-child relationship with the parent in the ProductAssemblyID column.
  • A clustered index for the BillOfMaterials table is defined over the ProductAssemblyID and ComponentID columns.
• The BOMLevel column indicates the level of a parent-child relationship within the hierarchy for a finished good.
  • The assemblies and components on BOMLevel 1 are at the level just below the level for a finished good. The level for a finished good is sometimes referred to as the root level of a hierarchy.
  • Each subsequent BOMLevel value below BOMLevel 1 denotes new set of assemblies based on the components from its prior level and a new set of child components (until there are no new assemblies that are, in turn, comprised of new components).
  • Within the AdventureWorks database, the root node for each finished good does not occupy a row in the BillOfMaterials table.
• Another especially important column is the PerAssemblyQty column. This column supports the recipe role of a bill of materials by specifying the quantity for the child row component in its parent assembly.
• The UnitMeasureCode column indicates how to interpret the number in the PerAssemblyQty. For example, EA is for each. Therefore, a row with a PerAssemblyQty column value of 10 and UnitMeasureCode column value of EA indicates that there are ten child components in the parent assembly on the row.
• Two other columns are the StartDate and EndDate columns.
  • A StartDate column value cannot be null. This non-null datetime data type value denotes the start date for including the child item in the recipe for the parent item.
  • The EndDate column also has a datetime data type, but it can include null values. The child item is included in the recipe for the parent assembly so long as the EndDate column value is null. A non-null EndDate column value denotes the last date for the inclusion of child item in a parent assembly.

The Product table in the AdventureWorks database has a separate row for each product associated with the AdventureWorks company. Not all rows in a Product table must refer to finished goods manufactured by a company. Some items may be for intermediate goods that are not finished goods meant for sale to customers. Other items may be purchased from suppliers meant for direct sale to customers and/or use in manufacturing finished goods as well. In contrast to the Product table, the BillOfMaterials table is strictly for internally manufactured finished goods.

The assembly (ProductAssemblyID) and component (ComponentID) columns in the BillOfMaterials table both point to the ProductID column in the Product table. Because the AdventureWorks company manufactures bikes, you can use the Product table to generate a list of all bikes with associated defining features manufactured by the company. Here’s a script that fulfills this role. The script identifies each bike by product name, size, weight, and color. Two additional columns are also included – namely the MakeFlag and FinishedGoodsFlag.

The following script left joins the ProductSubcategory table to the Product table. Additionally, the script left joins the ProductCategory table to the ProductSubcategory table.

• The Name column from the ProductCategory table allows the selection of just rows for bikes from the Product table. The where clause implements this filter. The Name column from the ProductCategory table also identifies each row in the result set through the cat_name alias as belonging to the Bikes category.
• The Name column from the ProductSubcategory table identifies each result set row as belonging to one of three bike subcategories.
• The Name column from the Product table indicates the name of the bike product in the prod_name column of the query’s result set.
• The order by clause in the following script sorts the bikes by product subcategory, prod_name, color and size.

use AdventureWorks2014
go
 
-- returns all ProductID values with product category name of Bikes
-- 97 products in the set
SELECT 
 [ProductID]
,[ProductCategory].Name cat_name
,ProductSubcategory.Name sub_cat_name
,[Product].Name prod_name
,[MakeFlag]
,[FinishedGoodsFlag]
,[Color]
,[Size]
,[SizeUnitMeasureCode]
,[WeightUnitMeasureCode]
,[Weight]
from [AdventureWorks2014].[Production].[Product]
left join AdventureWorks2014.Production.ProductSubcategory
on Product.ProductSubcategoryID = ProductSubcategory.ProductSubcategoryID
left join AdventureWorks2014.Production.ProductCategory 
on ProductCategory.ProductCategoryID = ProductSubcategory.ProductCategoryID
where 
[ProductCategory].Name = 'Bikes'
order by Product.ProductSubcategoryID, prod_name ,color, size

Here’s the first and last forty-three rows in result set from the preceding query. These are enough rows to understand the content in the result set. There is a total of ninety-seven rows in the complete result set.

• Each row has a unique ProductID column value denoting a distinct bike manufactured by the AdventureWorks company.
• The cat_name column value is Bikes because all rows point to one of the manufactured bikes.
• There are three subcategories of bikes:
  • Mountain Bikes reside in rows 1 through 32 for a count of 32 bikes
  • Road Bikes reside in rows 33 through 75 for a count of 43 bikes
  • Touring Bikes reside in rows 76 through 97 for a count of 22 bikes
• All rows have a value of one for both MakeFlag and FinishedGoodsFlag columns. This is because the AdventureWorks company manufactures all the bikes that it sells. Also, all bikes are sold as finished goods.
• The total selection of colors for bikes include black, silver, red, yellow, and blue. Not all bike subcategories come in each color.
• Bike frames come in a variety of tube sizes ranging from a minimum of 38 centimeters through a maximum of 48 centimeters. Not all bike subcategories are manufactured in the same collection of frame sizes.
• Bikes also come in a variety of different weights from below 14 US pounds through 30 US pounds.

Here’s a query for another selection of product names. The cat_name column indicates all these products are components. The prod_name values include "ped", " Derailleur", or "seat".

-- another selection of product names that are not manufactured bikes
SELECT 
 [ProductID]
,[ProductCategory].Name cat_name
,ProductSubcategory.Name sub_cat_name
,[Product].Name prod_name
,[MakeFlag]
,[FinishedGoodsFlag]
,[Color]
,[Size]
,[SizeUnitMeasureCode]
,[WeightUnitMeasureCode]
,[Weight]
from [AdventureWorks2014].[Production].[Product]
left join AdventureWorks2014.Production.ProductSubcategory
on Product.ProductSubcategoryID = ProductSubcategory.ProductSubcategoryID
left join AdventureWorks2014.Production.ProductCategory 
on ProductCategory.ProductCategoryID = ProductSubcategory.ProductCategoryID
where 
[ProductCategory].Name != 'Bikes' and 
([Product].Name like '%ped%' or
 [Product].Name like '%Derailleur%' or
 [Product].Name like '%seat%')
order by Product.ProductSubcategoryID, prod_name ,color, size

Here’s the result set from the preceding query. All these products are assemblies in the BillOfMaterials table, but they have a product category value of components in the Product table. These eighteen products can also be purchased by customers as finished goods – just like bikes. The front and rear derailleur assemblies, which serve a purpose like gears on a car, are made internally. However, the remaining sixteen components are purchased from suppliers to the AdventureWorks company. One of the roles for a bill of materials application can be to facilitate communication between manufacturing firms and their suppliers.

In the process of building a bill of materials application, you are likely to find it useful to query a bill of materials table. In the AdventureWorks database, there is a single table for the different assemblies and components of all finished goods. An engineer or product designer will be responsible for developing the content for such a table. Recall that the rows in a bill of materials for any finished good comprise a recipe for the finished good. The recipe denotes a set of hierarchical relations between assemblies and components at multiple manufacturing levels.

The following query returns the components for the bike with a ProductID value of 775. The filter requiring EndDate values of null ensures that your result set only contains current components.

• All rows have a ProductAssemblyID value of 775. This value points to the top-level parent of each component in the result set.
• The full set of tables providing values to the result set are in the from clause. These tables include BillOfMaterials, Product, and ProductSubcategory.
• The ComponentID value for each row points to a distinct product in the Product table. The result set assigns comp_name as the name for a component. The comp_name value is from the Name column of the Product table with a ProductID value matching the ComponentID value in the BillOfMaterials table.
• The result set sorts rows by the Name column from the ProductSubcategory table.

-- components from [BillOfMaterials] and name from Product
-- for ProductID (ProductAssemblyID) = 775 (Mountain-100 Black, 38)
-- along with other columns from other tables
select [BillOfMaterialsID]
      ,[ProductAssemblyID]
      ,[ComponentID]
      ,Product.Name comp_name
      ,ProductSubcategory.Name subcat_name
      ,[Product].[MakeFlag]
      ,[Product].[FinishedGoodsFlag]
      ,Product.[DaysToManufacture]
      ,[EndDate]
      ,[UnitMeasureCode]
      ,[BOMLevel]
      ,[PerAssemblyQty]
from [AdventureWorks2014].[Production].[BillOfMaterials] 
left join [AdventureWorks2014].[Production].[Product]
   on BillOfMaterials.ComponentID = Product.ProductID
left join [AdventureWorks2014].Production.ProductSubcategory
   on ProductSubcategory.ProductSubcategoryID = Product.ProductSubcategoryID
where ProductAssemblyID = 775 and EndDate is null
order by ProductSubcategory.Name

Here’s the result rows from the preceding query.

• There are 14 components for the top-most assembly for the bike with a ProductAssemblyID value of 775. Again, the top-most assembly is the bike.
• You can tell all rows are for the top-most assembly because the BOMLevel value is 1 for all rows.
• You can scan the values in the MakeFlag column to identify which components are made internally and which components are purchased from external suppliers. A value of zero denotes a component purchased from an external supplier.
• The component 516 is unlike all the other components listed below in that it cannot be purchased as a finished good by a customer.
• One day per component is generally allowed for the manufacturing of components, but one component (747) has an allowed manufacturing time of two days.

Getting all the BOM rows for a product

The prior example displayed the assembly-component relations for the top-level assembly for the bike with a ProductID value of 775. Another more common objective is to list all the BOM (bill of material) rows for a bike. This section presents a process for assigning hierarchyid values to all the BOM rows that pertain to a root node. In the context of this tip, the root node can be any of the bikes manufactured by the AdventureWorks company.

Before diving into the details of how to return all bill of material table rows for a finished good, lets briefly examine the excerpt below with sample output for an AdventureWorks bike. The following Results tab excerpt presents summary information from the process for extracting BOM rows for a finished good. The Results tab below shows five panes – one for each of five queries extracting results from the BillOfMaterials table and associated other tables needed to populate result set columns. The following bullets give highlights about the columns in each result set.

• The rows are all for the first bike in the list of bikes shown in the "Getting familiar with products, assemblies, and components for a bill of materials" section; this bike has a ProductID value of 775 and a name of "Mountain- 100 Black, 38".
• The assembly_name column reveals the name of the parent assembly for each row.
• Successive panes show results for successive BOMLevel values; the process ceases when there are no rows returned for a BOMLevel. This outcome occurs for the fifth result set in the following Results tab.
• The Ancestor_NodeString and Comp_NodeString columns contain slash-decimal value representations of the hierarchyid values for each row.
  • Because of the slashes, the slash-decimal value representation has a variable-length character data type in SQL Server.
  • The Ancestor_NodeString column value shows the parent node string value.
  • The Comp_NodeString column value shows the child node string value.
• The ProductAssemblyID and ComponentID columns display the parent and child ProductID for each BOM row.
• The other columns provide the BOMLevel, count of components per row, and other information to help identify child node on the row.

The number of slashes in the Comp_NodeString column is dependent on the BOMLevel.

• The Comp_NodeString values for BOMLevel 1 rows have a single pair of slashes delimiting a single decimal such as, /1/, /2/, … , /14/. These hierarchyid values are for the final components used to assemble the product designated by the assembly_name column in BOMLevel 1. The numbers within slashes denote the left-to-right hierarchy position for listing the components used to assemble the bike denoted by the assembly name for BOMLevel 1 rows.
• The Comp_NodeString values for BOMLevel 2 rows have a leading slash, a trailing slash, and a middle slash separating two decimal numbers. Within this level, there is more than one parent assembly. This is because each parent assembly with a BOMLevel 2 value corresponds to one of the components from BOMLevel 1.
• The Comp_NodeString values for BOMLevels 3 and 4 also start and end with slashes. Each decimal value represents the left-to-right listing position for a component node relative to a parent node.
  • There are three decimal values within Comp_NodeString values for each BOMLevel 3 row.
  • There are four decimal values within Comp_NodeString values for each BOMLevel 4 row.

Here’s an overview of the process used to generate result sets like the one described above.

• Start by extracting BOM rows for components at BOMLevel 1. All these rows will have a ProductAssemblyID value matching the ProductID for the finished good to which the rows apply. In the preceding screen shot the ProductAssemblyID value is 775. You can specify the final finished good with a local variable inside the script. The 775 ProductID row in the Product table has a Name column value of "Mountain-100 Black, 38".
• The query for the second pane in the preceding display shows all the component children nodes for each of the component child nodes from BOMLevel 1. For example, the preceding excerpt with rows for BOMLevel 2 shows ProductAssemblyID values of 516, 945, and 952. All these node identifiers and other ComponentID node values from BOMLevel 1 were for component child nodes in the preceding BOMLevel.
• Repeat the process of extracting child nodes from the preceding BOMLevel and using them as parent nodes in the current node until you encounter a BOMLevel for which there are no child nodes.

The following T-SQL script contains code for implementing the above process. Because the root node is a local variable to which you can assign a ProductID value, you can readily return results for any of the ninety-seven bikes manufactured by the AdventureWorks company.

• The @root_pid local variable towards the top of the script specifies 775 as the ProductID value for the parent node in the parent-child node pairs within BOMLevel 1. You can change the @root_pid local variable assignment to the ProductID value for any other finished good for which you want a bill of materials.
• The #bom_level_1_comps local temp table holds rows for BOMLevel 1.
  • The select statement to populate the temp table draws on the BillOfMaterials, Product, ProductCategory, and ProductSubcategory tables in the AdventureWorks database. An into clause instantiates and populates the temp table. A couple of string expressions designate values for the Ancestor_NodeString and Comp_ NodeString select list items. A nested subquery in the select item list populates the assembly_name column based on the @root_pid value.
  • A second select statement displays the rows in the #bom_level_1_comps local temp table.
• The code block for rows from the BOMLevel 2 draws on two temp tables (#bom_level_2_comps_wo_cns and #bom_level_2_comps) that are instantiated and populated by two different select statements. A third select statement displays the rows in the #bom_level_2_comps temp table.
  • The first select statement for the #bom_level_2_comps_wo_cns temp table draws data from BillOfMaterials table rows with ProductAssemblyID column values matching the child components from the #bom_level_1_comps local temp table. The first select statement adds two new columns to the BillOfMaterials columns.
    • One of the new columns has the name productassemblyid_pid, which has ProductID values for ProductAssemblyID values from the BillOfMaterials table matching the child components from the #bom_level_1_comps local temp table.
    • A second new column named c_w_a has sequential row number values for successive ComponentID values partitioned within ProductAssemblyID values. This new column provides values for populating slash-decimal representations for rows in the #bom_level_2_comps temp table. You can reference this link for a tutorial on the row_number function.
  • The second select statement instantiates and populates the #bom_level_2_comps temp table. This table has the same format (but with different values) as the #bom_level_1_comps temp table.
    • The from clause contains a left join of the #bom_level_1_comps temp table to the #bom_level_2_comps_wo_cns temp table. Additionally, a where clause restricts the rows to those with a value of 2 for BOMLevel.
    • The select statement has list items
      • Calculated from the concatenation of Comp_NodeString from BOMLevel 1 and the c_w_a value from #bom_level_2_comps_wo_cns temp
      • inherited from the #bom_level_1_comps temp table for Comp_NodeString, cat_name, and sub_cat_name
      • derived from a nested subquery for assembly_name
      • extracted from the #bom_level_2_comps_wo_cns temp table for the remaining columns
• The code blocks for rows from BOMLevel 3, 4, and 5 closely follow the design of code for rows from BOMLevel 2.
  • First, a temp table named #bom_level_x_comps_wo_cns is instantiated and populated for the current BOMLevel. The values of x are successively 3, 4, and 5.
  • Second, a temp table named #bom_level_x_comps is instantiated and populated for the current BOMLevel. The two sources for the #bom_level_x_comps temp table are the #bom_level_x_comps_wo_cns for the current BOMLevel and #bom_level_y_comps for the preceding BOMLevel. The values of x are successively 3, 4, and 5. The values of y are successively 2, 3, and 4.
  • The queries for BOM rows cease at BOMLevel 5 because no rows populated the #bom_level_x_comps temp table for that BOMLevel.

It is possible to include some of the iteration described above within a WHILE loop, but I am hoping the walkthrough of the iteration elements will help others to adapt this process to the resource tables for their own bill of material environments. At the very least, this presentation demonstrates that you can derive hierarchyid values for a bill of materials table originally specified by an engineer or a product designer serving in that capacity.

use AdventureWorks2014
go
 
-- declare root node productid; the root node denotes a bike from Product table
declare @root_pid int = 775
 
---------------------------------------------------------------------------------------------
 
begin try
drop table #bom_level_1_comps
end try
begin catch
print 'drop table for #bom_level_1_comps did not succeed'
end catch
 
-- ComponentID rows from BillOfMaterials table 
-- for bike with ProductID of 775
 
-- bom level 1 with cns (component node string) 
SELECT 
 '/' Ancestor_NodeString
,'/' + cast(ROW_NUMBER() over (order by ProductID) as nvarchar(20)) + '/' Comp_NodeString
,[ProductCategory].Name cat_name
,ProductSubcategory.Name sub_cat_name
,(select name from Production.Product where ProductID = @root_pid) assembly_name
,[BillOfMaterialsID]
,[ProductAssemblyID]
,[ComponentID]
,[UnitMeasureCode]
,[BOMLevel]
,[PerAssemblyQty]
into #bom_level_1_comps
from [AdventureWorks2014].[Production].[BillOfMaterials]
left join AdventureWorks2014.Production.Product
   on [BillOfMaterials].ProductAssemblyID = Product.ProductID
left join AdventureWorks2014.Production.ProductSubcategory
   on Product.ProductSubcategoryID = ProductSubcategory.ProductSubcategoryID
left join AdventureWorks2014.Production.ProductCategory 
on ProductCategory.ProductCategoryID = ProductSubcategory.ProductCategoryID
where ProductAssemblyID = @root_pid  and EndDate is null
 
-- display #bom_level_1_comps
select * from #bom_level_1_comps order by Comp_NodeString
 
---------------------------------------------------------------------------------------------
 
-- returns boms for components in 775 at BOMLevel of 2
begin try
drop table #bom_level_2_comps_wo_cns
end try
begin catch
print 'drop table for #bom_level_2_comps_wo_cns did not succeed'
end catch
 
begin try
drop table #bom_level_2_comps
end try
begin catch
print 'drop table for #bom_level_2_comps did not succeed'
end catch
 
-- bom level 2 without cns (component node string)
select 
 ROW_NUMBER() over (partition by productassemblyid order by ComponentID) c_w_a
,ProductAssemblyID productassemblyid_pid
,* 
 
into #bom_level_2_comps_wo_cns
from [AdventureWorks2014].[Production].[BillOfMaterials] where ProductAssemblyID in
(
select componentID 
from [AdventureWorks2014].[Production].[BillOfMaterials] 
where EndDate is null and [ProductAssemblyID] = @root_pid
)
  
-- bom level 2 with cns (component node string) and selected fields from level 1
select 
 #bom_level_1_comps.Comp_NodeString  Ancestor_NodeString
,#bom_level_1_comps.Comp_NodeString + cast(#bom_level_2_comps_wo_cns.c_w_a as nvarchar(20)) + '/' Comp_NodeString
,#bom_level_1_comps.cat_name
,#bom_level_1_comps.sub_cat_name
,(select name from Production.Product where ProductID = #bom_level_2_comps_wo_cns.ProductAssemblyID) assembly_name
,#bom_level_2_comps_wo_cns.BillOfMaterialsID
,#bom_level_2_comps_wo_cns.ProductAssemblyID
,#bom_level_2_comps_wo_cns.ComponentID
,#bom_level_2_comps_wo_cns.UnitMeasureCode
,#bom_level_2_comps_wo_cns.BOMLevel
,#bom_level_2_comps_wo_cns.PerAssemblyQty
into #bom_level_2_comps
from #bom_level_2_comps_wo_cns
left join #bom_level_1_comps 
on #bom_level_2_comps_wo_cns.ProductAssemblyID = #bom_level_1_comps.ComponentID
where #bom_level_2_comps_wo_cns.BOMLevel=2
 
-- display #bom_level_2_comps and drop #bom_level_2_comps_wo_cns
select * from #bom_level_2_comps order by Comp_NodeString
drop table #bom_level_2_comps_wo_cns
 
---------------------------------------------------------------------------------------------
 
-- returns boms for components in 775 at BOMLevel of 3
--select * from #bom_level_2_comps_wo_cns
 
begin try
drop table #bom_level_3_comps_wo_cns
end try
begin catch
print 'drop table for #bom_level_3_comps_wo_cns did not succeed'
end catch
 
begin try
drop table #bom_level_3_comps
end try
begin catch
print 'drop table for #bom_level_3_comps did not succeed'
end catch
 
-- bom level 3 without cns (component node string)
select 
 ROW_NUMBER() over (partition by productassemblyid order by ComponentID) c_w_a
,ProductAssemblyID productassemblyid_pid
,* 
 
into #bom_level_3_comps_wo_cns
from [AdventureWorks2014].[Production].[BillOfMaterials] where ProductAssemblyID in
(
select ComponentID from #bom_level_2_comps --where EndDate is null
)
 
-- bom level 3 with cns (component node string) and selected fields from level 2
select 
 #bom_level_2_comps.Comp_NodeString  Ancestor_NodeString
,#bom_level_2_comps.Comp_NodeString + cast(#bom_level_3_comps_wo_cns.c_w_a as nvarchar(20)) + '/' Comp_NodeString
 
,#bom_level_2_comps.cat_name
,#bom_level_2_comps.sub_cat_name
,(select name from Production.Product where ProductID = #bom_level_3_comps_wo_cns.ProductAssemblyID) assembly_name
 
,#bom_level_3_comps_wo_cns.BillOfMaterialsID
,#bom_level_3_comps_wo_cns.ProductAssemblyID
,#bom_level_3_comps_wo_cns.ComponentID
,#bom_level_3_comps_wo_cns.UnitMeasureCode
,#bom_level_3_comps_wo_cns.BOMLevel
,#bom_level_3_comps_wo_cns.PerAssemblyQty
 
into #bom_level_3_comps
from #bom_level_3_comps_wo_cns
left join #bom_level_2_comps 
on #bom_level_3_comps_wo_cns.ProductAssemblyID = #bom_level_2_comps.ComponentID
where #bom_level_3_comps_wo_cns.BOMLevel=3
 
-- display #bom_level_3_comps and drop #bom_level_3_comps_wo_cns
select * from #bom_level_3_comps order by Comp_NodeString
drop table #bom_level_3_comps_wo_cns
 
---------------------------------------------------------------------------------------------
 
-- returns boms for components in 775 at BOMLevel of 4
 
begin try
drop table #bom_level_4_comps_wo_cns
end try
begin catch
print 'drop table for #bom_level_4_comps_wo_cns did not succeed'
end catch
 
begin try
drop table #bom_level_4_comps
end try
begin catch
print 'drop table for #bom_level_4_comps did not succeed'
end catch
 
-- bom level 4 without cns (component node string)
select 
 ROW_NUMBER() over (partition by productassemblyid order by ComponentID) c_w_a
,ProductAssemblyID productassemblyid_pid
,* 
 
into #bom_level_4_comps_wo_cns
from [AdventureWorks2014].[Production].[BillOfMaterials] where ProductAssemblyID in
(
select ComponentID from #bom_level_3_comps --where EndDate is null
)
 
-- bom level 4 with cns (component node string) and selected fields from level 3
select 
 #bom_level_3_comps.Comp_NodeString  Ancestor_NodeString
,#bom_level_3_comps.Comp_NodeString + cast(#bom_level_4_comps_wo_cns.c_w_a as nvarchar(20)) + '/' Comp_NodeString
 
,#bom_level_3_comps.cat_name
,#bom_level_3_comps.sub_cat_name
,(select name from Production.Product where ProductID = #bom_level_4_comps_wo_cns.ProductAssemblyID) assembly_name
 
,#bom_level_4_comps_wo_cns.BillOfMaterialsID
,#bom_level_4_comps_wo_cns.ProductAssemblyID
,#bom_level_4_comps_wo_cns.ComponentID
,#bom_level_4_comps_wo_cns.UnitMeasureCode
,#bom_level_4_comps_wo_cns.BOMLevel
,#bom_level_4_comps_wo_cns.PerAssemblyQty
 
into #bom_level_4_comps
from #bom_level_4_comps_wo_cns
left join #bom_level_3_comps 
on #bom_level_4_comps_wo_cns.ProductAssemblyID = #bom_level_3_comps.ComponentID
where #bom_level_4_comps_wo_cns.BOMLevel=4
 
-- display #bom_level_4_comps and drop #bom_level_4_comps_wo_cns
select * from #bom_level_4_comps order by Comp_NodeString
drop table #bom_level_4_comps_wo_cns
 
---------------------------------------------------------------------------------------------
 
-- returns boms for components in 775 at BOMLevel of 5
-- does not return rows
 
begin try
drop table #bom_level_5_comps_wo_cns
end try
begin catch
print 'drop table for #bom_level_5_comps_wo_cns did not succeed'
end catch
 
begin try
drop table #bom_level_5_comps
end try
begin catch
print 'drop table for #bom_level_5_comps did not succeed'
end catch
 
-- bom level 5 without cns (component node string)
select 
 ROW_NUMBER() over (partition by productassemblyid order by ComponentID) c_w_a
,ProductAssemblyID productassemblyid_pid
,* 
 
into #bom_level_5_comps_wo_cns
from [AdventureWorks2014].[Production].[BillOfMaterials] where ProductAssemblyID in
(
select ComponentID from #bom_level_4_comps --where EndDate is null
)
 
-- bom level 5 with cns (component node string) and selected fields from level 4
select 
 #bom_level_4_comps.Comp_NodeString  Ancestor_NodeString
,#bom_level_4_comps.Comp_NodeString + cast(#bom_level_5_comps_wo_cns.c_w_a as nvarchar(20)) + '/' Comp_NodeString
,#bom_level_4_comps.cat_name
,#bom_level_4_comps.sub_cat_name
,(select name from Production.Product where ProductID = #bom_level_5_comps_wo_cns.ProductAssemblyID) assembly_name
,#bom_level_5_comps_wo_cns.BillOfMaterialsID
,#bom_level_5_comps_wo_cns.ProductAssemblyID
,#bom_level_5_comps_wo_cns.ComponentID
,#bom_level_5_comps_wo_cns.UnitMeasureCode
,#bom_level_5_comps_wo_cns.BOMLevel
,#bom_level_5_comps_wo_cns.PerAssemblyQty
into #bom_level_5_comps
from #bom_level_5_comps_wo_cns
left join #bom_level_4_comps 
on #bom_level_5_comps_wo_cns.ProductAssemblyID = #bom_level_4_comps.ComponentID
where #bom_level_5_comps_wo_cns.BOMLevel=5
 
-- display #bom_level_5_comps and drop #bom_level_5_comps_wo_cns
select * from #bom_level_5_comps order by Comp_NodeString
drop table #bom_level_5_comps_wo_cns

The presentation and discussion of the output from the preceding script appears at the beginning of this section. I did some sample testing of the script to see if it generated valid results for another bike (ProductID 965), and the script appeared to generate valid results. This is because the components for bike finished goods from the AdventureWorks company share highly similar design elements. In your own applications of this approach to BOM applications that you encounter, you should independently assess the degree to which a finished goods collection shares common component specifications.

• When the elements in a collection of finished goods share the same number and distribution by BOMLevel parent-child node relationships, then you can use the process once to discover a query for all BOM rows across any of a set of finished goods.
• When the BOMLevel parent-child node relationships are different across a set of finished goods, then you should run the process to get a new query for each new finished good.

Also, the process up until this point only assigns hierarchyid values with the slash-decimal format. While this format is easy for humans to read and understand, it is not the optimal way that SQL Server internally stores hierarchyid values. The other way of representing hierarchyid values is with hexadecimal characters that designate bit strings. Converting hierarchyid values from slash-decimal to hexadecimal characters has considerable value. SQL Server processes a slash-decimal instance as a sequence of variable-length character values, but SQL Server processes a hexadecimal instance as a numeric value. Therefore, when you sort rows by hexadecimal character values, they appear in numeric order instead of string order as is the case for the BOM rows at the beginning of this section. Furthermore, hierarchy level values can be readily derived from hexadecimal representations via the GetLevel method for hierarchyid values. Also, it is easier to perform filtering for hierarchyid values based on single numeric values instead of variable length sequences of decimal characters representing values.

You can convert slash-decimal format values to hexadecimal values with the parse method for the hierarchyid data type. SQL Server data types do not normally have methods, but the hierarchyid data type does have both methods as well as a property, and these are handy for many purposes; see some of these methods discussed in this prior tip (An Introduction to Representing and Querying Data with the Hierarchyid Data Type). This link provides a resource from the Microsoft site on hierarchyid data type methods.

Here’s a code excerpt that illustrates the syntax for converting slash-decimal representations of hierarchyid values to hexadecimal representations. The code also performs several other common functions described in the bullet summary for the code below.

• The syntax for the parse method is as follows: hierarchyid::Parse(slash-decimal representation). The method accepts a slash-decimal hierarchy representation for a hierarchyid value and returns its corresponding hexadecimal representation.
• Another critical feature of the code below is to concatenate with union operators the four separate result sets for BOM rows at BOMLevel values from 1 through 4 into a single temp table named #comps_with_nodes. This outcome simplifies processing all the BOM rows for a finished good in one step.
• The code below illustrates the use of the GetLevel method when applied to hierarchyid values. This method derives the hierarchy level for any set of nodes represented by hierarchyid values in hexadecimal format. As it turns out, hierarchy level values should match BOMLevel values. You can use this feature to unit check your assignment hierarchyid values to BOM rows.
• A select statement towards the end of the following script adds one new column to the result set in the #comps_with_nodes temp table and displays the new result set with the transformed hierarchyid values and the new column for component names on each of the BOM rows.

-- convert slash-decimal strings to hexadecimal strings
-- while concatenating level result sets
begin try
drop table #comps_with_nodes
end try
begin catch
print 'drop table for #comps_with_nodes did not succeed'
end catch
 
-- union rows from #bom_level_1_comps through 
-- #bom_level_5_comps to instantiate and populate
-- the #comps_with_nodes local temp table
-- with a hexadecimal format instead of slash-decimal format
-- for representing hierarchyid values
-- also add hierarchyid level to the result set with the GetLevel method
select * 
into #comps_with_nodes
from 
(
select 
 hierarchyid::Parse(Ancestor_NodeString) Ancestor_Node
,hierarchyid::Parse(Comp_NodeString) Comp_Node
,hierarchyid::Parse(Comp_NodeString).GetLevel() Comp_Node_Level
,* 
from #bom_level_1_comps
union
select 
hierarchyid::Parse(Ancestor_NodeString) Ancestor_Node
,hierarchyid::Parse(Comp_NodeString) Comp_Node
,hierarchyid::Parse(Comp_NodeString).GetLevel() Comp_Node_Level
,* 
from #bom_level_2_comps
union
select
 hierarchyid::Parse(Ancestor_NodeString) Ancestor_Node
,hierarchyid::Parse(Comp_NodeString) Comp_Node
,hierarchyid::Parse(Comp_NodeString).GetLevel() Comp_Node_Level
,* 
from #bom_level_3_comps
union
select 
 hierarchyid::Parse(Ancestor_NodeString) Ancestor_Node
,hierarchyid::Parse(Comp_NodeString) Comp_Node
,hierarchyid::Parse(Comp_NodeString).GetLevel() Comp_Node_Level
,* 
from #bom_level_4_comps
union
select
 hierarchyid::Parse(Ancestor_NodeString) Ancestor_Node
,hierarchyid::Parse(Comp_NodeString) Comp_Node
,hierarchyid::Parse(Comp_NodeString).GetLevel() Comp_Node_Level
, * 
from #bom_level_5_comps
) node_string
 
 
-- listing of nodes without order by clause
-- a nested subquery computes a new column named comp_name
select 
 Ancestor_Node
,Comp_Node
,Comp_Node_Level
,assembly_name
,(select Name from Production.Product where ProductID = #comps_with_nodes.ComponentID) comp_name
,BillOfMaterialsID
,ProductAssemblyID
,ComponentID
,UnitMeasureCode
,BOMLevel
,PerAssemblyQty
from #comps_with_nodes

The following script shows the first forty rows of the concatenated BOM rows with hexadecimal representations for hierarchyid values.

• The total number of rows in the #comps_with_nodes temp table is 89. This outcome matches the total number of rows across temp tables #bom_level_1_comps through #bom_level_4_comps.
• You can verify the listing of rows by matching rows from the following listing with those from BOM rows populating the four temp tables at the beginning of this section. This is yet another way to unit test your assignment of hierarchyid values to rows.
  • For example, the highlighted row in the screen shot below has a BillOfMaterialsID value of 2000. This row is for a ComponentID value of 2 that is a child to a ProductAssemblyID value of 3. Also, the PerAssemblyQty column value is 10, and the BOMLevel value is 3.
  • The screen shot of BOM rows at the beginning of this section has as its first row from the #bom_level_3_comps temp table a row with a BillOfMaterialsID value of 2000. This row also has matching values for ComponentID, BOMLevel, and PerAssemblyQty columns.
  • Therefore, the second row from the listing below matches the row from the #bom_level_3_comps temp table at the beginning of this section. Ancestor_Node and Comp_Node values for the highlighted row below are in hexadecimal format, and Ancestor_NodeString and Comp_NodeString from the #bom_level_3_comps temp table listing appear in slash-decimal format.

Sort orders for BOM rows with hexadecimal hierarchyid values

One typical sort for hierarchyid values is to order them by the hierarchy level value that is encoded within them. This sort order displays rows with the lowest level value before showing those with the next higher level and so on. Recall that for both BOMLevel and Comp_Node_Level the top level has a value of 1 followed by 2 for the next higher code level and so forth across subsequent levels. This kind of sort order generates what is called a breadth-first listing of the rows. For a BOM application, the top level is particularly important in the sense that you cannot assemble a finished good unless all the top-level components are in stock and ready to be put together to manufacture a finished good.

The following script shows how to generate a breadth-first list for the BOM rows in the #comps_with_nodes temp table plus the added column to show comp_name. The added column shows the name for the values of the built-in ComponentID column, which shows the ProductID value for the components.

 -- breadth-first listing of nodes (Order by Comp_Node_Level)
 select 
 Ancestor_Node
,Comp_Node
,Comp_Node_Level
,assembly_name
,(select Name from Production.Product where ProductID = #comps_with_nodes.ComponentID) comp_name
,BillOfMaterialsID
,ProductAssemblyID
,ComponentID
,UnitMeasureCode
,BOMLevel
,PerAssemblyQty
from #comps_with_nodes order by Comp_Node_Level			

The next screen shot shows an excerpt from the result set generated by the preceding script. There are eighty-nine BOM rows in total for the Mountain- 100 Black, 38 finished good, but only fourteen of these rows have a Comp_Node_Level value of 1. The bike name appears as the assembly name for all top-level rows. All the components in these rows are top-level components in the sense that the bike cannot be assembled without them being in inventory and ready to be assembled in a way that manufactures the Mountain- 100 Black, 38 finished good. A red border highlights these BOM rows while it shows a very simple application of the hierarchyid values in manufacturing applications -- namely, isolating the top-level components.

A depth-first listing of BOM rows sorts by the hierarchyid value for their component, which is Comp_Node value in this example. The sort for this kind of listing requires that the hierarchyid value for rows be expressed in a hexadecimal character format. The sort by Comp_Node value arranges components one after the other in the order that they are needed to complete a top-level assembly of the components for a finished good. Assembling starts from the bottom of the order and progresses to the top of the list to generate a top-level component.

Here’s the script for a depth-first list of the BOM rows. Notice that the order by clause references the Comp_Node column.

 -- depth first listing of nodes (order by Component_Node)
 select 
 Ancestor_Node
,Comp_Node
,Comp_Node_Level
,assembly_name
,(select Name from Production.Product where ProductID = #comps_with_nodes.ComponentID) comp_name
,BillOfMaterialsID
,ProductAssemblyID
,ComponentID
,UnitMeasureCode
,BOMLevel
,PerAssemblyQty
from #comps_with_nodes order by Comp_Node			

The next screen shot shows an excerpt from the result set for the preceding script. The block of rows 6 through 30 are highlighted. All these rows pertain either directly or indirectly to the component with a hierarchyid value (Comp_Node) of 0x68, which is for the frame component of the Mountain- 100 Black, 38 bike.

• Rows 7 and 8 denote components for the Chain Stays for the bike frame.
  • In Row 8, a Metal Sheet 5 component is crafted into a Chain Stays component.
  • In Row 7, the Chain Stays component is attached to other bike frame components to complete the bike frame (HL Mountain Frame – Black, 38).
• Row 6 depicts the frame as a component for the top-level assembly, which has a hierarchyid value of 0x.
• Some components are designated as parts of an assembly in a single step. For example, row 15 is for the application of 8 ounces of black paint to the bike frame parent.
• Other components apply directly and indirectly to the frame as part of a coordinated set of components. Rows 23 through 30 illustrate this kind of relationship between the HL Fork component and the bike frame assembly. The HL Fork component becomes a part of the frame, but the HL Fork is itself composed of multiple components at two lower hierarchy levels.

The next query extracts a subset of the full set of BOM rows that are just for the frame of the Mountain- 100 Black, 38 bike. A where clause in the query extracts just Comp_Node column values from rows 6 through 30 from the preceding result set. These are the rows that refer directly or indirectly to the frame component, which is a top-level component for the assembly of the Mountain-100 Black, 38 bike.

The following query mixes the breadth-first and depth-first listing of components and adds a new sort by dimension as well. The first column name in the order by clause is Comp_Node_Level. This column name by itself would implement a breadth-first listing. However, Comp_Node_Level is followed by Comp_Node in the order by clause. As a result, the rows appear in depth-first order within levels. Finally, the third dimension is added via a nested select statement that orders rows by component name. This arranges the components by alphabetical name order within an assembly stage after they are ordered by Comp_Node_Level and Comp_Node.

-- depth-first listing within breadth-first listing of Components
-- arranged by component name for HL Mountain Frame - Black, 38
select 
 Ancestor_Node
,Comp_Node
,assembly_name
,(select Name from Production.Product where ProductID = #comps_with_nodes.ComponentID) comp_name
,PerAssemblyQty
,UnitMeasureCode
,Comp_Node_Level
from #comps_with_nodes 
where Comp_Node in
(0x68
,0x6AC0
,0x6AD6
,0x6B40
,0x6BC0
,0x6C20
,0x6C2B
,0x6C60
,0x6C6B
,0x6CA0
,0x6D10
,0x6D1680
,0x6D30
,0x6D3580
,0x6D50
,0x6D5580
,0x6D70
,0x6D7580
,0x6D75AC
,0x6D7680
,0x6D76AC
,0x6D7840
,0x6D7856
,0x6D78C0
,0x6D78D6
)
order by 
 Comp_Node_Level, Comp_Node
,(select Name from Production.Product where ProductID = #comps_with_nodes.ComponentID)			

Here’s an image of the result set from the preceding query copied to a tab in an Excel workbook.

• Comp_Node_Level values are in column G. As dictated by the order by clause, these values start with 1 and extend in order through 4.
• Comp_Node values are in column B. The order by clause dictates that Comp_Node values within column B appear from lowest to highest value within a Comp_NodeLevel value. For example, rows from Comp_Node_Level 2 all have 0x68 as their parent Ancestor_Node in rows 3 through 12. The Comp_Node column values within column B appear in order within these rows. The row 3 Comp_Node value is 0x6AC0 followed by 0x6B40 in row 4 through 0x6D70 in row 12.
• Finally, rows are sorted by comp_name within comp_Node_Level and comp_Node. The comp_name sort order can only be shown when it does not conflict with the Comp_Node_Level, and Comp_Node sort orders. For the rows in the screen shot below, the comp_name sort order is always overridden by either or both of the two preceding order criteria. For example, there are four rows with a Comp_Node_Level value of 4.
  • The rows clearly appear in comp_Node order with the row having a comp_Node 0x6D75AC value appearing first and the node having a comp_Node 0x6D76AC value appearing second. As you can tell by computing the value for the hexadecimal characters to decimal or binary numbers, the row with the smaller comp_Node value appears before the row with the larger Comp_Node value.
  • On the other hand, the Metal Sheet5 comp_name value should appear after the Metal Sheet 2 value on an alphanumeric sort basis, but the comp_Node criterion overrides the comp_name criterion.

Another advantage of the preceding kind of result set is that it facilitates the preparation of hierarchical charts that show a valid order for assembling components across levels. The following chart shows the preparation of a hierarchical chart for the components with results from rows 2 through 12 of the Comp Sheet tab in the preceding screen shot.

• The column values from D through F for row 2 are formatted in the left-most box. In this case, the left-most box is for the completed HL Mountain-100 Frame – Black, 38 component. This is a top-level component for completing the assembly of the Mountain-100 Black, 38 bike.
• The column values from D through F for rows 3 through 12 appear in the ten second-tier boxes to the right of the left-most box. The text values in these boxes are not formatted.

The next screen shot shows a completed hierarchical chart for the components in the Comp Sheet tab; the chart has some formatting to facilitate its presentation. Components are assembled in right-to-left order.

• Therefore, the first four assemblies are for combining fourth-tier components to the third-tier components to which the chart connects them.
• The next round of assemblies consists of combining third-tier components to the second-tier components to which the chart connects them.
• The last round of assemblies consists of combining the ten second-tier components to the first-tier assembly (HL Mountain Frame – Black, 38).
• The first-tier assembly in the chart below serves as one of the fourteen components in BOMLevel 1 for the Mountain-100 Black, 38 bike assembly.

Next Steps

• If you do not have the AdventureWorks2014 database downloaded, consider downloading the database to make the source data readily available for the code in this tip. You can download this database from the Microsoft SQL Docs site.
• If you would like a refresher tip on the hierarchyid data type, see this prior tip (An Introduction to Representing and Querying Data with the Hierarchyid Data Type).
• There are three types of scripts in this tip.
  • Introductory scripts to help you become familiar with manufacturing data from a bill of material perspective: See particularly the scripts in the "Getting familiar with products, assemblies, and components for a bill of materials" section.
  • Scripts to add hierarchyid values for previously existing bill of material tables, such as the BillOfMaterials tables in the AdventureWorks2014 database: One script in the "Getting all the BOM rows for a product" section shows how to generate and apply slash-decimal format hierarchyid values to the BillOfMaterials table. The second script in the "Getting all the BOM rows for a product" section shows how to convert slash-decimal hierarchyid values to hexadecimal hierarchyid values.
  • Three scripts in the "Sort orders for BOM rows with hexadecimal hierarchyid values" section demonstrate selected benefits for bill of materials applications delivered with hierarchyid values in hexadecimal format: The scripts highlight the value of sorting hierarchyid values according to different criteria. The third script in the section is accompanied by a hierarchical chart created and displayed in an Excel workbook file that points to specific steps in the manufacturing process based on a bill of materials table with hierarchyid values. The Excel workbook is available as a download with this tip.
• Consider re-designing the scripts in this tip so that they apply to your bill of materials data.
• Next, build a series of sample scripts demonstrating to the manufacturing team within your organization the benefits of the hierarchyid data type for bill of material applications.
• Finally, build stored procedures and other T-SQL code containers to make your demonstration scripts ready for easy re-use by others. See this tutorial for an introduction to programming stored procedures.

About the author

Rick Dobson is an author and an individual trader. He is also a SQL Server professional with decades of T-SQL experience that includes authoring books, running a national seminar practice, working for businesses on finance and healthcare development projects, and serving as a regular contributor to MSSQLTips.com. He has been growing his Python skills for more than the past half decade -- especially for data visualization and ETL tasks with JSON and CSV files. His most recent professional passions include financial time series data and analyses, AI models, and statistics. He believes the proper application of these skills can help traders and investors to make more profitable decisions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips