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
sqdperuAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
Yes, it is possible.

Just have to get around constructing it where you can use dynamic SQL.

The key is getting the unique sys_mfg_workctr.gate_id values that will be used for the column headers.

That's where it gets a bit complicated because it needs to be multiple steps.

I would suggest the first step is to save the results of the query into a temp table, then use that to drive the pivot using dynamic sql.

Something like : 
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

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 for xml path('')) ,1,1,'')

print @columns

declare @sql varchar(max)

set @sql = 'select * from
(select 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

I have written Articles about pivot and dynamic SQL : http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_653-Dynamic-Pivot-Procedure-for-SQL-Server.html
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sqdperuAuthor Commented:
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
0
sqdperuAuthor Commented:
Just what I needed.  Thanks Mark!
0
Mark WillsTopic AdvisorCommented:
My pleasure and very pleased that you could incorporate your order by. Well done :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.