One thing that I still get confused about writing is pivot queries. I find myself needing to lookup the syntax every time. Basically you use Pivot and Unpivot to change the output of a table. If you would like rows turned into columns you can use pivot and for the opposite you can use unpivot.

One thing to note is the column identifiers in the unpivot clause follow the catalog collation. For SQL Database, the collation is always SQL_Latin_General_CP1_CI_AS. For SQL Server partially contained databases, the collation is always Latin1_General_100_CI_AS_KS_WS_SC. If the column is combined with other columns, then a collate clause (COLLATE DATABASE_DEFAULT) is required to avoid conflicts.

So lets take a basic select statement:

USE AdventureWorks2014 ;  
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost   
FROM Production.Product  
GROUP BY DaysToManufacture;

This returns the following:

DaysToManufacture AverageCost
----------------- -----------
0                 5.0885
1                 223.88
2                 359.1082
4                 949.4105

To pivot the table you can write this kind of query:

-- Pivot table with one row and five columns  
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,   
[0], [1], [2], [3], [4]  
(SELECT DaysToManufacture, StandardCost   
    FROM Production.Product) AS SourceTable  
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])  
) AS PivotTable;  

This returns the following:

Cost_Sorted_By_Production_Days 0           1           2           3           4         
------------------------------ ----------- ----------- ----------- ----------- -----------
AverageCost                    5.0885      223.88      359.1082    NULL        949.4105

As you can see it places the average values of the standard cost into the columns specified 0-4.

Here is an example of an Unpivot query:

-- Create the table and insert values as portrayed in the previous example.  
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,  
    Emp3 int, Emp4 int, Emp5 int);  
INSERT INTO pvt VALUES (1,4,3,5,4,4);  
INSERT INTO pvt VALUES (2,4,1,5,5,5);  
INSERT INTO pvt VALUES (3,4,3,5,4,4);  
INSERT INTO pvt VALUES (4,4,2,5,5,4);  
INSERT INTO pvt VALUES (5,5,1,5,5,5);  
-- Unpivot the table.  
SELECT VendorID, Employee, Orders  
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5  
   FROM pvt) p  
   (Orders FOR Employee IN   
      (Emp1, Emp2, Emp3, Emp4, Emp5)  
)AS unpvt;  

Here is a part of the result set:

VendorID    Employee    Orders
----------- ----------- ------
1            Emp1       4
1            Emp2       3 
1            Emp3       5
1            Emp4       4
1            Emp5       4
2            Emp1       4
2            Emp2       1
2            Emp3       5
2            Emp4       5
2            Emp5       5

Remember that unpivot is not the exact reverse of a pivot query. The pivot query uses aggregations and therefore merges possible multiple rows into a single row in the output.

Hope this helps you with your pivot queries!

6 thoughts on “Pivot and Unpivot

  1. Pingback: Pivoting And Unpivoting Data In T-SQL – Curated SQL

  2. I too have found it difficult to remember the syntax for Pivot and Unpivot, thankfully we have Google at our disposal to refresh our memory. These are very powerful constructs to remember. I have been able to help colleagues solve a number of problems by using these approaches to change the shape of their existing result set so that the data would be more usable for their use case.

  3. I’m having trouble on returning values for this, can you help! I get only NULL for d1-d4.
    SELECT medication_name AS med,
    [d1], [d2], [d3], [d4]
    (SELECT medication_name, icd_code_id
    FROM TA_PIVOT) AS SourceTable
    FOR icd_code_id IN ([d1], [d2], [d3], [d4])
    ) AS PivotTable;

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.