SQL Server Pivot Operator

The SQL Server Pivot is one of the most useful operators to convert the Row values into Column names or, say, rotating tables. While rotating the table or the Pivot Table, only the column values that are involved in Grouping or Aggregation are allowed to be moved. This Pivot Table tutorial will help you understand how to use the pivot table wizard.

USE AdventureWorks2014
GO
SELECT PROD.Name,
       YEAR(OrdHead.OrderDate) AS [Order Year],
       SUM(Details.OrderQty) AS [Order Quantity]
  FROM Sales.SalesOrderDetail AS Details
     INNER JOIN
       Production.Product AS PROD ON
          Details.ProductID = PROD.ProductID
     INNER JOIN 
       Sales.SalesOrderHeader AS OrdHead ON
         Details.SalesOrderID = OrdHead.SalesOrderID
GROUP BY 
         PROD.Name,
         YEAR(OrdHead.OrderDate)
ORDER BY [Order Year]

We’re going to copy the original data into a new table. This will prevent any problems if our source data changes. I think it’s essential that you master the SQL Server PIVOT clause as it can be used to perform calculations on columns of data. When you do this, you are using the SELECT INTO statement. Use the PIVOT function to change data into columns.

Here’s a look at our new table data:

PIVOT Operator Example

This example will convert the date to separate months and years so that each row of data will contain a month and year value. If you’re ordering by Product Name, your Order quantities will be separated into columns based on the Year.

SELECT Name,[2011], [2012], [2013], [2014]
FROM (
      SELECT [Name]
           ,[Order Year]
           ,[Order Quantity]
      FROM [PSource]
     ) AS SOURCE
PIVOT 
   ( 
    SUM([Order Quantity]) FOR [Order Year] 
	 IN ([2011], [2012], [2013], [2014])
   ) AS [Result] 
ORDER BY Name

Using the Select query we just wrote, you can retrieve all the data from the SalesOrders table. Please refer to the SELECT statement and the SELECT INTO statement in SQL Server.

After this, the remaining questions after the SOURCE will be converted into columns using the aggregate function SUM and then pivoted. This query displays the output.

SELECT Name,[2011], [2012], [2013], [2014]

You need to use the following query to get the data into the format that you require it in.

USE AdventureWorks2014
GO
SELECT Name,[2011], [2012], [2013], [2014]
 FROM 
   (
    SELECT PROD.Name,
           YEAR(OrdHead.OrderDate) AS OrderYear,
           SUM(Details.OrderQty) AS OrderQuantity
    FROM Sales.SalesOrderDetail AS Details
     INNER JOIN Production.Product AS PROD 
       ON Details.ProductID = PROD.ProductID
     INNER JOIN Sales.SalesOrderHeader AS OrdHead 
       ON Details.SalesOrderID = OrdHead.SalesOrderID
     GROUP BY PROD.Name,YEAR(OrdHead.OrderDate)
    ) AS SOURCE
PIVOT( SUM(OrderQuantity) FOR OrderYear 
        IN ([2011], [2012], [2013], [2014])
       ) AS Result
ORDER BY Name

Another example

Suppose that you are getting all your information from one table. If your source data doesn’t include any Joins and Grouping. You could try this approach:

SELECT Name,[2011], [2012], [2013], [2014]
FROM [PSource]
PIVOT 
   ( 
    SUM([Order Quantity]) FOR [Order Year] 
      IN ([2011], [2012], [2013], [2014])
   ) AS [Result] 
ORDER BY Name

PIVOT Alternative

This alternative query example is purely for interview purposes. You should start by reading the question carefully. For example, if the question asks you to convert a row into column format, you don’t have to start.

SELECT 
    Name
   ,SUM(CASE WHEN [Order Year] = 2011 THEN [Order Quantity] ELSE 0 END)AS [2011]
   ,SUM(CASE WHEN [Order Year] = 2012 THEN [Order Quantity] ELSE 0 END)AS [2012]
   ,SUM(CASE WHEN [Order Year] = 2013 THEN [Order Quantity] ELSE 0 END)AS [2013]
   ,SUM(CASE WHEN [Order Year] = 2014 THEN [Order Quantity] ELSE 0 END)AS [2014]  
FROM [PSource]    
GROUP BY Name
ORDER BY Name

If you want to use the same functionality on your original data, you need to use this query:

USE AdventureWorks2014
GO
SELECT 
   PROD.Name
  ,SUM(CASE WHEN YEAR(OrdHead.OrderDate) = 2011 THEN (Details.OrderQty) ELSE 0 END)AS [2011]
  ,SUM(CASE WHEN YEAR(OrdHead.OrderDate) = 2012 THEN (Details.OrderQty) ELSE 0 END)AS [2012]
  ,SUM(CASE WHEN YEAR(OrdHead.OrderDate) = 2013 THEN (Details.OrderQty) ELSE 0 END)AS [2013]
  ,SUM(CASE WHEN YEAR(OrdHead.OrderDate) = 2014 THEN (Details.OrderQty) ELSE 0 END)AS [2014]
FROM Sales.SalesOrderDetail AS Details
  INNER JOIN Production.Product AS PROD 
    ON Details.ProductID = PROD.ProductID
  INNER JOIN Sales.SalesOrderHeader AS OrdHead 
    ON Details.SalesOrderID = OrdHead.SalesOrderID
GROUP BY PROD.Name
ORDER BY Name