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_idFROM (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
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_pivotSELECT 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_pivotFROM (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 srcDECLARE @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 @columnsDECLARE @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 @sqlEXEC (@sql)
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:
Open in new window
Here is the output:
Thanks