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
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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

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
Code




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
Code

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



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.