sqdperu
asked on
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Just what I needed. Thanks Mark!
My pleasure and very pleased that you could incorporate your order by. Well done :)
ASKER
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