CODEWITHSUNDEEP
sqlsql-serverpivot
sunny
sunny

Reputation: 11

Pivoting in SQL

I would like some help pivoting. I have a dataset in SQL that looks like this

ID-----------VisitDate----------Metric------Value  
1001---------2012-01-01---------Cajun-------40  
1001---------2012-01-02---------Cajun-------30  
1001---------2012-01-01---------Ham---------20  
1003---------2012-01-02---------Ham---------10  
1003---------2012-01-03---------Beef--------10

How can I pivot this dataset so that I transform it from long to wide format based on the ID and VisitDate Columns, so the dataset would look something like this:

ID-----------VisitDate----------Cajun------Ham--------Beef  
1001---------2012-01-01---------40---------20---------Null  
1001---------2012-01-02---------30---------Null-------Null  
1003---------2012-01-02---------Null-------10---------Null  
1003---------2012-01-03---------Null-------Null-------10

Upvotes: 0

Views: 84

Answers (3)

Saravana Kumar
Saravana Kumar

Reputation: 3729

Use PIVOT to get the result. Check the result in Fiddler

Ref. to learn SQL SERVER – PIVOT and UNPIVOT Table Examples

SELECT id, 
       visitdate, 
       SUM([Cajun]) AS [Cajun], 
       SUM([Ham]) AS [Ham],
       SUM([beef]) AS [beef] 
FROM Test AS A
PIVOT(MIN(A.value) FOR A.metric IN ([Cajun],[Ham],[beef])) AS B
GROUP BY id, visitdate

Upvotes: -1

YS.
YS.

Reputation: 1828

Here's a link to sqlfiddle: http://sqlfiddle.com/#!3/0445e/1

Here's how it looks like using PIVOT:

SELECT
  ID,
  VisitDate,
  Cajun,
  Ham,
  Beef
FROM (  
  SELECT
    ID,
    VisitDate,
    Metric,
    Value
  FROM
    Bleh
) AS SourceTable PIVOT (
  MAX (Value) FOR Metric IN (Cajun, Ham, Beef)
) AS PivotTable

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

If you're sure that the value of Metric will only consists of Cajun, Ham and Beef, then this will do it:

SELECT
    Id,
    VisitDate,
    Cajun = MAX(CASE WHEN Metric = 'Cajun' THEN Value END),
    Ham = MAX(CASE WHEN Metric = 'Ham' THEN Value END),
    Beef = MAX(CASE WHEN Metric = 'Beef' THEN Value END)
FROM YourTable
GROUP BY ID, VisitDate
ORDER BY ID, VisitDate

On the other hand, if you don't know the value of Metric, then you can use a Dynamic Cross Tab. For reference: http://www.sqlservercentral.com/articles/Crosstab/65048/

DECLARE @sql1 VARCHAR(4000) = ''
DECLARE @sql2 VARCHAR(4000) = ''
DECLARE @sql3 VARCHAR(4000) = ''

SELECT @sql1 = 
'SELECT
    ID
    ,VisitDate'
+ CHAR(10)

SELECT @sql2 = @sql2 +
'   ,MAX(CASE WHEN Metric = ''' + Metric + ''' THEN Value END) AS [' + Metric + ']' + CHAR(10)
FROM(
    SELECT DISTINCT Metric FROM YourTable
)t

SELECT @sql3 =
'FROM YourTable
GROUP BY ID, VisitDate
ORDER BY ID, VisitDate
'
PRINT(@sql1 + @sql2 + @sql3)
EXEC (@sql1 + @sql2 + @sql3)

RESULT

ID          VisitDate  Beef        Cajun       Ham
----------- ---------- ----------- ----------- -----------
1001        2012-01-01 NULL        40          20
1001        2012-01-02 NULL        30          NULL
1003        2012-01-02 NULL        NULL        10
1003        2012-01-03 10          NULL        NULL

Upvotes: 3

Related Questions