three alternate solution for PIVOTing records

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 404
CREATE TABLE PlantMetrics
(PlantID int
,TransDt datetime
,Metric char (4)
,Value nvarchar (10)
,CONSTRAINT pk_plmt PRIMARY KEY CLUSTERED (PlantID, TransDt, Metric)
);
GO
INSERT PlantMetrics
VALUES (1,'7/1/09', 'M', '24')
,(1,'7/1/09', 'W', 'Sunny')
,(1,'7/1/09', 'T', '88')
,(2,'7/1/09', 'T', '94')
,(1,'7/2/09', 'M', '36')
,(3,'7/2/09', 'D', '7')
GO
SELECT * FROM PlantMetrics
GO
-- First approach using LEFT JOIN
SELECT p.PlantID
, P.TransDt
, M.Value AS M
, T.Value AS T
, W.Value AS W
, D.Value AS D
FROM PlantMetrics p
LEFT OUTER JOIN (SELECT * FROM PlantMetrics WHERE Metric = 'M') AS M
ON p.PlantID = M.PlantID AND p.TransDt = M.TransDt
LEFT OUTER JOIN (SELECT * FROM PlantMetrics WHERE Metric = 'T') AS T
ON p.PlantID = T.PlantID AND p.TransDt = T.TransDt
LEFT OUTER JOIN (SELECT * FROM PlantMetrics WHERE Metric = 'W') AS W
ON p.PlantID = T.PlantID AND p.TransDt = T.TransDt
LEFT OUTER JOIN (SELECT * FROM PlantMetrics WHERE Metric = 'D') AS D
ON p.PlantID = T.PlantID AND p.TransDt = T.TransDt
GROUP BY P.PlantID, P.TransDt, M.Value, T.Value, W.Value, D.Value
ORDER BY PlantID, TransDt
GO
-- second approach
SELECT PlantID
, TransDt
, MAX(M) as M
, MAX(T) AS T
, MAX(W) AS W
, MAX(D) AS D
FROM ( SELECT PlantID, TransDt, Value as M, NULL AS W, NULL AS T, NULL AS D
FROM PlantMetrics
WHERE Metric = 'M' UNION ALL SELECT PlantID, TransDt, NULL AS M, Value AS W, NULL AS T, NULL AS D
FROM PlantMetrics
WHERE Metric = 'W' UNION ALL SELECT PlantID, TransDt, NULL AS M, NULL AS W, Value AS T, NULL AS D
FROM PlantMetrics
WHERE Metric = 'T' UNION ALL SELECT PlantID, TransDt, NULL AS M, NULL AS W, NULL AS T, Value AS D
FROM PlantMetrics
WHERE Metric = 'D' ) as x
GROUP BY PlantID, TransDt
ORDER BY PlantID, TransDt
GO
-- Best solution using PIVOT
SELECT * FROM PlantMetrics
PIVOT ( MAX(VALUE) FOR Metric IN (M, W, T, D)) X
ORDER BY PlantID, TransDt
GO
DROP TABLE PlantMetrics

Comments or Responses

Login to post response