troubleshooting Question

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

Avatar of sqdperu
sqdperuFlag for United States of America asked on
Microsoft SQL ServerSQL
4 Comments1 Solution98 ViewsLast Modified:
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

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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros