SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2017
download SQL Server 2016
download SQL Server 2014



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 :

bill of material bom sql query using t-sql pivot



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






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems