Avatar of sqdperu
sqdperu
Flag for United States of America asked on

MS SQL, how do I pivot the query results (dynamic column names)

I have an MS SQL query that will have dynamic columns that I would like to pivot the results.  I am not sure how code the SQL to do this.  The column names will vary in name and number of columns.  One order may have 4 columns.  The next order may 5 columns and the names (gate_id) can be different in each case.

This is the query:
SELECT TOP (100) PERCENT 
       CASE 
          WHEN confirmed > 0 THEN CAST(confirmed AS decimal(8)) / CAST(operations AS decimal(8))  
	      ELSE 0 
	   END AS Attn, 
	   gate_id, so_id, plant_id
FROM (SELECT TOP (100) PERCENT dbo.mfg_order_oper.plant_id, dbo.mfg_order_oper.so_id, COUNT(dbo.mfg_order_oper.oper_id) AS operations, 
             COUNT(CASE WHEN oper_end IS NOT NULL THEN 1 END) AS confirmed, dbo.sys_mfg_workctr.gate_id, dbo.sys_mfg_gate.gate_seq
      FROM dbo.mfg_order_oper 
	  INNER JOIN dbo.mfg_order 
	    ON dbo.mfg_order_oper.plant_id = dbo.mfg_order.plant_id AND dbo.mfg_order_oper.order_nbr = dbo.mfg_order.order_nbr 
	  INNER JOIN dbo.sys_mfg_workctr 
	    ON dbo.mfg_order_oper.plant_id = dbo.sys_mfg_workctr.plant_id AND dbo.mfg_order_oper.workctr = dbo.sys_mfg_workctr.workctr 
	  LEFT OUTER JOIN dbo.sys_mfg 
	    ON dbo.sys_mfg_workctr.mfg_id = dbo.sys_mfg.mfg_id AND dbo.sys_mfg_workctr.plant_id = dbo.sys_mfg.plant_id 
	  INNER JOIN dbo.sys_mfg_gate 
	    ON dbo.sys_mfg_workctr.gate_id = dbo.sys_mfg_gate.gate_id AND dbo.sys_mfg_gate.mfg_id = dbo.sys_mfg_workctr.mfg_id
      WHERE (dbo.mfg_order.cancelled = 0 AND dbo.mfg_order_oper.plant_id = '123' AND dbo.mfg_order_oper.so_id = '9876543210' AND dbo.sys_mfg.mfg_ref = 1) OR
            (dbo.sys_mfg.mfg_ref IS NULL AND dbo.mfg_order_oper.plant_id = '123' AND dbo.mfg_order_oper.so_id = '9876543210')
      GROUP BY dbo.mfg_order_oper.plant_id, dbo.mfg_order_oper.so_id, dbo.sys_mfg_workctr.gate_id, dbo.sys_mfg_gate.gate_seq
	  ORDER BY dbo.sys_mfg_gate.gate_seq) AS src

Open in new window


The results look like this:
Results.png
I would like to pivot them to look like this:
pivot.png
Is this possible with MS SQL server (2014)?   If so, what is the syntax?

Thanks
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
Mark Wills

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Mark Wills

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
sqdperu

ASKER
Mark,

I forgot to mentioned the results need to stay in the same order as my ORDER BY clause on gate_seq.  I figured that part out.

Thanks for the help.  It got we what I needed.

Here is the final query:
IF object_id('tempdb..#tmp_mfg_pivot','u') IS NOT Null DROP TABLE #tmp_mfg_pivot

SELECT TOP (100) PERCENT 
       CASE 
          WHEN confirmed > 0 THEN CAST(confirmed AS decimal(8)) / CAST(operations AS decimal(8))  
	      ELSE 0 
	   END AS Attn, 
	   gate_id, so_id, plant_id, gate_seq  
INTO #tmp_mfg_pivot
FROM (SELECT TOP (100) PERCENT dbo.mfg_order_oper.plant_id, dbo.mfg_order_oper.so_id, COUNT(dbo.mfg_order_oper.oper_id) AS operations, 
             COUNT(CASE WHEN oper_end IS NOT NULL THEN 1 END) AS confirmed, dbo.sys_mfg_workctr.gate_id, dbo.sys_mfg_gate.gate_seq
      FROM dbo.mfg_order_oper 
	  INNER JOIN dbo.mfg_order 
	    ON dbo.mfg_order_oper.plant_id = dbo.mfg_order.plant_id AND dbo.mfg_order_oper.order_nbr = dbo.mfg_order.order_nbr 
	  INNER JOIN dbo.sys_mfg_workctr 
	    ON dbo.mfg_order_oper.plant_id = dbo.sys_mfg_workctr.plant_id AND dbo.mfg_order_oper.workctr = dbo.sys_mfg_workctr.workctr 
	  LEFT OUTER JOIN dbo.sys_mfg 
	    ON dbo.sys_mfg_workctr.mfg_id = dbo.sys_mfg.mfg_id AND dbo.sys_mfg_workctr.plant_id = dbo.sys_mfg.plant_id 
	  INNER JOIN dbo.sys_mfg_gate 
	    ON dbo.sys_mfg_workctr.gate_id = dbo.sys_mfg_gate.gate_id AND dbo.sys_mfg_gate.mfg_id = dbo.sys_mfg_workctr.mfg_id
      WHERE (dbo.mfg_order.cancelled = 0 AND dbo.mfg_order_oper.plant_id = '123' AND dbo.mfg_order_oper.so_id = '9876543210' AND dbo.sys_mfg.mfg_ref = 1) OR
            (dbo.sys_mfg.mfg_ref IS NULL AND dbo.mfg_order_oper.plant_id = '123' AND dbo.mfg_order_oper.so_id = '9876543210')
      GROUP BY dbo.mfg_order_oper.plant_id, dbo.mfg_order_oper.so_id, dbo.sys_mfg_workctr.gate_id, dbo.sys_mfg_gate.gate_seq
	  ORDER BY dbo.sys_mfg_gate.gate_seq) AS src

DECLARE @columns varchar(MAX)

SET @columns = STUFF((SELECT ',['+gate_id+']' FROM #tmp_mfg_pivot GROUP BY gate_id, gate_seq ORDER BY gate_seq FOR XML PATH('')) ,1,1,'')

--PRINT @columns

DECLARE @sql varchar(MAX)

SET @sql = 'SELECT * FROM
(SELECT TOP (100) PERCENT CAST(Attn * 100 AS Integer) AS Attn, gate_id FROM #tmp_mfg_pivot) src  
PIVOT
(SUM(Attn) FOR gate_id IN ('+@columns+')) pvt'

--PRINT @sql

EXEC (@sql)

Open in new window


Here is the output:
FinalResults.png
Thanks
sqdperu

ASKER
Just what I needed.  Thanks Mark!
Mark Wills

My pleasure and very pleased that you could incorporate your order by. Well done :)
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23