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:
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?
Thanks
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:
I would like to pivot them to look like this:
Is this possible with MS SQL server (2014)? If so, what is the syntax?
Thanks
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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 :
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)
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:
Here is the output:
Thanks
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)
Here is the output:
Thanks
Premium Content
You need an Expert Office subscription to comment.Start Free Trial