Pivoting data is not something I usually do in SQL Server as it is typically easier to create a query and connect it to a Pivot Table using Excel. Unfortunately, the data users have minimal expertise with Excel, and a simple report will suffice for now.
A Pivot Table transposes rows into columns where the rows of a given column become columns of the result set. Use SQL Server Management Studio to write and execute the queries as applicable to your database tables.
The articles titled “SQL Server PIVOT Operator Explained Clearly By Practical Examples (sqlservertutorial.net)” and “Dynamic Pivot Tables in SQL Server (sqlshack.com)” both provide an excellent tutorial to Pivot data in SQL Server and include a “dynamic” pivot solution as well.
The first article cited above presents a Dynamic Pivot solution, however, we recommend using the DISTINCT qualifier in the select statement to ensure unique row entries are transposed to columns.
OrderRouting is the name of the table containing our process data including step numbers and quantities of parts produced at each step. The output of the above SQL query is as follows:
Our manufacturing database contains a job routing table that captures the part number and quantity of parts produced at each process step. Our initial objective is to summarize the number of parts made against a given job number for each process step, as pictured below.
Our Query appears as follows:
USE [YOUR_DATABASE_NAME]
GO
-- USE CHAR(39) or 3 apostrophe characters ' to surround strings for proper insertion of
-- the text into the SQL Statement created below.
DECLARE @JobStatus NVARCHAR(8) = '''Open'''
DECLARE @OrderStatus NVARCHAR(3) = '''O'''
DECLARE @Columns NVARCHAR(MAX) = ''
-- QUOTENAME is a built in function that places []'s around the column name
SELECT @Columns += QUOTENAME(Steps.StepNo) + ','
FROM (
SELECT DISTINCT StepNo
FROM OrderRouting
) as Steps
Order By Steps.StepNo
SET @Columns = LEFT(@Columns, LEN(@Columns) - 1)
PRINT @Columns
--/*
DECLARE @SQLQuery NVARCHAR(MAX) = '
-- Select Base Data for Pivoting
-- Create a Temporary Result Set Using a Derived Table
SELECT CustCode
, PartNo
, JobNo
, '+ @Columns +'
FROM (
-- DATA TO PIVOT StepNo and ActualPcs Good
-- From OrderRouting
SELECT --< PIVOT THIS DATA - Quantity of Good Parts at Each StepNo >--
StepNo
, COALESCE(ActualPcsGood,0) as ActualPcsGood
--<Columns To Group On>--
, o.CustCode
, r.PartNo
, r.JobNo
FROM OrderRouting r
INNER JOIN
OrderDet d
ON r.JobNo = d.JobNo
INNER JOIN
Orders o
ON d.OrderNo = o.OrderNo
WHERE d.[Status] = ' + @JobStatus + ' -- JobNo Status = Open or Closed
AND
o.[Status] = ' + @OrderStatus + ' -- Order Status = O or C
AND
NOT r.ActualPcsGood IS NULL -- Ensure ALL Data is VALID
) as t
PIVOT
( SUM(ActualPcsGood)
FOR StepNo IN (' + @Columns +')
) AS pivot_table
ORDER BY CustCode, PartNo, JobNo;';
PRINT @SQLQuery
--*/
-- Remove the comment from the line below to EXECUTE the Query above.
--EXECUTE sp_executesql @SQLQuery;
The following is the result of executing the above code:
The above code yields the number of parts produced at each process step. The ultimate goal is to determine the actual quantity of parts remaining at each process step. With a little manipulation using the LEAD windowing function, this is easily achieved.