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.