Create A Sample Bill of Material BOM SQL Query using T-SQL Pivot Command
Bill of Material aka BOM is a list of materials used in the manufacturing of a product.
If you keep BOM data in your MS SQL Server databases, sql developers or SQL Server DBAs probably are required to display the items of a product in the pivot table format frequently.
In this pivot table tutorial or in this pivot tables sample, I will first create a dummy as well as simple BOM table, then populate the BOM sql table with sample data.
Then we will develop a t-sql code using PIVOT command in order to display BOM data in pivot table format.
Here is the CREATE TABLE sql script for BOM table.
CREATE TABLE ProductMaterials
(
ProductCode nvarchar(50),
MaterialCode nvarchar(50),
Quantity int
)
Now we can fill simple BOM (bill of material) table with sample data.
INSERT INTO ProductMaterials SELECT N'P01', N'M01', 5
INSERT INTO ProductMaterials SELECT N'P01', N'M02', 10
INSERT INTO ProductMaterials SELECT N'P02', N'M03', 1
INSERT INTO ProductMaterials SELECT N'P03', N'M01', 10
INSERT INTO ProductMaterials SELECT N'P03', N'M02', 1
INSERT INTO ProductMaterials SELECT N'P03', N'M04', 2
INSERT INTO ProductMaterials SELECT N'P04', N'M05', 4
T-SQL BOM Query using Pivot Command
Below I have copied a sample t-sql query which displays BOM items for a product horizantally in the order according to the number of items specified in the BOM.
The below pivot table query considers items up to 3.
If you want to display more columns as pivot columns then you can change the pivot columns listed in the IN(...) part of the PIVOT table declaration.
SELECT *
FROM
(
SELECT
ProductCode,
RN = ROW_NUMBER() OVER (PARTITION BY ProductCode ORDER BY Quantity DESC),
MaterialCode
FROM ProductMaterials
) AS BOM
PIVOT
(
MIN(MaterialCode)
FOR
RN IN ([1],[2],[3])
) AS Pivots
The output of the above pivot table t-sql query is as follows :
T-SQL Pivot Table and Dynamic Pivot Table Queries in SQL Server
T-SQL Pivot Tables in SQL Server - Pivot Table Tutorial with Examples
T-SQL Dynamic Pivot Table Examples for SQL Server 2008
Create A Sample Bill of Material BOM SQL Query using T-SQL Pivot Command