Last Updated on August 15, 2022 by Sean Lee

Microsoft has introduced PIVOT and UNPIVOT with the release of SQL Server 2005.

We use PIVOT queries when we need to transform data from row-level to columnar data.

PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

PIVOT Example 1

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

Here is the result set:

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

No products are defined with three (3) DaysToManufacture.

The following code displays the same result, pivoted so that the DaysToManufacture values become the column headings. A column is provided for three [3] days, even though the results are NULL.

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

Here is the result set:

PIVOT Example 2

First Create a table named [sales].[Customers]


USE [AdventureWorks2017]
GO
-- CREATE TABLE
CREATE TABLE [sales].[Customers](
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[StandardDiscountPercentage] [int] NOT NULL,
[CreditLimit] [decimal](18,2) NULL,
CONSTRAINT [PK_SalesCustomer_CustomerID] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)
)

Then add some data to the table:


INSERT INTO [sales].[Customers]
(StandardDiscountPercentage, CreditLimit)
VALUES
(0, 800.08 ),
(0, 860.08 ),
(1, 1000.18 ),
(1, 1800.18 ),
(2, 2000.28 ),
(2, 2600.28 ),
(3, 3000.38 ),
(3, 3800.38 ),
(3, 3100.38 ),
(4, 4000.48 ),
(4, 4200.48 ),
(5, 5000.58 );

Here is the result set:

CustomerID  StandardDiscountPercentage CreditLimit
----------- -------------------------- ------------
1           0                          800.08
2           0                          860.08
3           1                          1000.18
4           1                          1800.18
5           2                          2000.28
6           2                          2600.28
7           3                          3000.38
8           3                          3800.38
9           3                          3100.38
10          4                          4000.48
11          4                          4200.48
12          5                          5000.58

(12 rows affected)

The following code displays the same result, pivoted so that the DiscountPercentage values become the column headings.

 -- Pivot table with one row and five columns
SELECT
[0], [1], [2], [3], [4]
FROM
(SELECT StandardDiscountPercentage,
        CreditLimit
FROM [sales].[Customers]) AS SourceTable
PIVOT
(
     AVG(CreditLimit)
     FOR StandardDiscountPercentage IN ([0], [1], [2], [3], [4])
) AS CreditLimitTable;

Here is the result set:

Practice Questions 1

You use Microsoft SQL Server 2012 database to develop a shopping cart application. You need to rotate the unique values of the ProductName field of a table-valued expression into multiple columns in the output.

Which Transact-SQL statement should you use?

A. CROSS JOIN

B. CROSS APPLY

C. PIVOT

 D. UNPIVOT

Answer & Explanation
Difficulty level:
Correct Answer: C

Explanation:

A, Wrong Cross join just list all the rows

B, Wrong CROSS APPLY will only return rows where there is a row in both the first table and the second table/function

C, Correct It will rotate the data into multiple columns in the output

D, Wrong UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

Practice Questions 2

You have a database that tracks orders and deliveries for customers in North America. System versioning is enabled for all tables. The database contains the Sales.Customers, Application.Cities, and Sales.CustomerCategories tables.

Details for the Sales.Customers table are shown in the following table:

Sales. Customers

Column	Data Type	Note
CustomerID	int	Primary Key
StandardDiscountPercentage	int	Does not allow NULL value
CreditLimit	Decimal(18,2)	NULL values are permitted

The marketing department is performing an analysis of how discount affect credit limits.

They need to know the average credit limit per standard discount percentage for customers whose standard discount percentage is between zero and four.

You need to write a query that returns the data for the analysis.

How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

Answer & Explanation
Difficulty level:
Correct Answer: See Below: