Pivot and Unpivot functions in SQL | Examples

In this tutorial, we will learn about the PIVOT and UNPIVOT functions in SQL. For learning this further, we have to be familiar with SQL.
SQL stands for Structured Query Language. It is used to create and access data from the database. For accessing the data from the database in SQL, we have to write a query. The query is basically a SELECT statement that is used to access the data from the database.

In SQL, Pivot and Unpivot are relational operators that are used to transform one table into another in order to achieve a simpler view of the table. Conventionally we can say that the Pivot operator converts the rows data of the table into the column data. The Unpivot operator does the opposite that is it transforms the column-based data into rows. SQL PIVOT is used to convert the rows into columnar data. So, it can rotate the table values by changing a single column into multiple columns.

This operator is used to rotate table-valued expressions. It was first introduced in SQL Server 2005 version converts data from rows to columns. It splits the unique values from one column into many columns and then aggregates the remaining column values required in the final result.

How to use the Pivot function in SQL

To get more details regarding below topics:
How to create a database in SQL
How to write the query in SQL

We must follow the following steps to create a PIVOT table :

Select the base dataset for pivoting.

Create temporary results with the help of a derived table or CTE (common table expression).

Make use of the PIVOT operator

Basic keywords in SQL

Below are some basic keywords in SQL.

SUM keyword in SQL

This operator is used to aggregate the values from the specified column to be used in the PIVOT table. We must use it with the PIVOT operator to get the aggregated column displays for the values sections.

FOR keyword in SQL

This keyword is used for the PIVOT table statement to instruct the PIVOT operator on which column the PIVOT function should be applied. Basically, it indicates the column names that will transform from rows to columns.

IN Keyword in SQL

IN keyword lists all the unique values from the PIVOT column to be displayed as the columns of the PIVOT table.

PIVOT function in SQL

PIVOT() function in SQL is used to modify our table for the easiest view of the table. It is used to convert row-based data into column-based data in a table.

Syntax

SELECT (ColumnNames) 
FROM (TableName) 
PIVOT
 ( 
   AggregateFunction(ColumnToBeAggregated)
   FOR PivotColumn IN (PivotColumnValues)
 ) AS (Alias)

For understanding a pivot() function first we have to create the table.
CREATE THE TABLE IN DBMS

Assume, we have the following STUDENT table :

Name	Salary	country
John	10000	USA
Robert	5000	USA
David	9000	UK
John	4500	UK
Betty	8000	UAE

Example :

-----------Create Product Table----------
CREATE TABLE [dbo].[tblProducts]
(
     [ProductName] [varchar](50) NULL,
     [Quantity] [int] NULL,
     [City] [nvarchar](50) NULL
) ON [PRIMARY]
 
-------------------------Insert data into the Product talbe----------------------------------
INSERT [dbo].[tblProducts] ([ProductName], [Quantity], [City]) VALUES (N'Laptop', 10, N'Noida')
INSERT [dbo].[tblProducts] ([ProductName], [Quantity], [City]) VALUES (N'Mobile', 12, N'Noida')
INSERT [dbo].[tblProducts] ([ProductName], [Quantity], [City]) VALUES (N'Desktop', 22, N'Noida')
INSERT [dbo].[tblProducts] ([ProductName], [Quantity], [City]) VALUES (N'Laptop', 23, N'Delhi')
INSERT [dbo].[tblProducts] ([ProductName], [Quantity], [City]) VALUES (N'Mobile', 34, N'Delhi')
INSERT [dbo].[tblProducts] ([ProductName], [Quantity], [City]) VALUES (N'Desktop', 7, N'Delhi')
INSERT [dbo].[tblProducts] ([ProductName], [Quantity], [City]) VALUES (N'Laptop', 87, N'Noida')
INSERT [dbo].[tblProducts] ([ProductName], [Quantity], [City]) VALUES (N'Mobile', 66, N'Noida')
INSERT [dbo].[tblProducts] ([ProductName], [Quantity], [City]) VALUES (N'Laptop', 45, N'Delhi')
 
------------------ Get all product records-------------------
SELECT City, ProductName, Quantity FROM tblProducts
 
------------------ Pivot on Product name column using order by city-------------------
SELECT City, Laptop, Mobile, Desktop
FROM (
SELECT City, ProductName, Quantity
FROM tblProducts
)  AS product
PIVOT 
(
SUM(Quantity)
FOR ProductName IN (Laptop, Mobile, Desktop)
) AS pvt ORDER BY City

Output :

UNPIVOT function in SQL

UNPIVOT() function is quite opposite to the PIVOT function. Both functions are used to modify the table. But the UNPIVOT function is used to convert the column-based data into row-based data.

Syntax

SELECT (ColumnNames) 
FROM (TableName) 
UNPIVOT
 ( 
   AggregateFunction(ColumnToBeAggregated)
   FOR PivotColumn IN (PivotColumnValues)
 ) AS (Alias)

Assume, we have the above Student table :

How to use Unpivot function in SQL

Let’s understand UNPIVOT with the help of the Example :

SELECT Name,Salary,country
FROM
(
SELECT Name,USA,UK
FROM Student
PIVOT 
( 
SUM(age) FOR Country IN (USA,UK) 
) AS Pivottable
) A
UNPIVOT 
( 
age FOR country IN (USA,UK)
) 
AS UnpivotTable