# three alternate solution for PIVOTing records

Posted by Bandi under Sql Server category on | Points: 40 | Views : 569
`CREATE TABLE PlantMetrics (PlantID int  ,TransDt datetime  ,Metric char (4)  ,Value nvarchar (10)  ,CONSTRAINT pk_plmt PRIMARY KEY CLUSTERED (PlantID, TransDt, Metric) );GOINSERT 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') GOSELECT * FROM PlantMetricsGO-- First approach using LEFT JOINSELECT p.PlantID    , P.TransDt    , M.Value AS M    , T.Value AS T    , W.Value AS W    , D.Value AS DFROM PlantMetrics pLEFT OUTER JOIN (SELECT * FROM PlantMetrics WHERE Metric = 'M') AS M         ON p.PlantID = M.PlantID AND p.TransDt = M.TransDtLEFT OUTER JOIN (SELECT * FROM PlantMetrics WHERE Metric = 'T') AS T         ON p.PlantID = T.PlantID AND p.TransDt = T.TransDtLEFT OUTER JOIN (SELECT * FROM PlantMetrics WHERE Metric = 'W') AS W         ON p.PlantID = T.PlantID AND p.TransDt = T.TransDtLEFT 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 approachSELECT PlantID    , TransDt    , MAX(M) as M    , MAX(T) AS T    , MAX(W) AS W    , MAX(D) AS DFROM ( 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 xGROUP BY PlantID, TransDtORDER BY PlantID, TransDtGO -- Best solution using PIVOTSELECT * FROM PlantMetricsPIVOT ( MAX(VALUE) FOR Metric IN (M, W, T, D)) XORDER BY PlantID, TransDt GODROP TABLE PlantMetrics`

#### Comments or Responses

Latest Code Snipptes