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:
          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:
I would like to pivot them to look like this:
Is this possible with MS SQL server (2014)?   If so, what is the syntax?

Mark Wills
Topic Advisor
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
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