Pivoting in SQL

Question

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

kind of, This is just a sample database, the real data table is about 3M rows. The value column are just random numeric values. What is known is the types of metrics — sunny
– sunny, Commented Feb 12, 2015 at 5:56

Felix Pamittan · Accepted Answer · 2015-02-12 05:22:57Z

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

YS. · Accepted Answer · 2015-02-12 05:42:02Z

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

Saravana Kumar · Accepted Answer · 2015-02-12 05:44:41Z

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

Collectives™ on Stack Overflow

Pivoting in SQL

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related