PIVOT AND UNPIVOT Operators in SQL Server

In this article, we are going to learn PIVOT and UNPIVOT Operators in SQL Server with some examples.

Introduction

The PIVOT operator in SQL Server converts data from row level to column level, and the UNPIVOT operator converts data from column level to row level.

We use PIVOT and UNPIVOT operators in SQL Server to generate multi-dimensional reports. These operators help us to sort and analyse data.

A pivot table consolidates a large group of data, allowing you to organize and draw better inferences more quickly.

Let’s begin to see how it works.

Firstly, we will create a sample dataset to understand how the PIVOT operator works in SQL Server.

CREATE TABLE StudentsReport
(
StudentName VARCHAR(50),
Subjects VARCHAR(50),
Marks INT
)

INSERT INTO StudentsReport VALUES('David','English',90)
INSERT INTO StudentsReport VALUES('David','Mathematics',98)
INSERT INTO StudentsReport VALUES('David','Science',98)
INSERT INTO StudentsReport VALUES('David','History',100)
INSERT INTO StudentsReport VALUES('Robert','English',95)
INSERT INTO StudentsReport VALUES('Robert','Mathematics',98)
INSERT INTO StudentsReport VALUES('Robert','Science',90)
INSERT INTO StudentsReport VALUES('Robert','History',90)

Now, let’s fetch the data using a SELECT query.

SELECT * FROM StudentsReport;

The sample dataset looks like below.

PIVOT Operator in SQL Server

Now let’s see how PIVOT Operator works in SQL Server. Below is the Syntax.

SELECT (ColumnNames) FROM  --List out the ColumnNames to be pivoted
	(SELECT ColumnNames FROM TableName) AS Alias --- Select data from table which already produced and Alias denotes temporary name for a table.
PIVOT
( 
   AggregateFunction(ColumnToBeAggregated) FOR PivotColumn IN (ListOfPivotedColumn)
) AS Alias

In the above syntax, we have used so many operators. Now, we will understand each operator one by one.

• AggregateFunction aggregates the value for the specified column, which is used in the PIVOT table.
• The FOR operator tells us in which column we need to apply the PIVOT function.
• IN operator lists out all the pivoted columns to add to the PIVOT Table.
• The PIVOT function is used to aggregate, group, calculate, sum, or average the data in the table.

Now, we will create a pivot table with an example considering the dataset StudentsReport. Let's run the query below to see how it is pivoting data from row level to column level.

SELECT StudentName, English, Mathematics, Science, History FROM 
	(SELECT StudentName, Subjects, Marks FROM StudentsReport) PIVOTTable1
PIVOT 
(
	SUM (Marks) FOR Subjects IN (English, Mathematics, Science, History)
) AS PIVOTTable2

In the above dataset, we can see that the PIVOT Operator has created a PIVOT table and converted the rows into columns.  In the Marks column, aggregate function SUM is applied, which calculates each subject for each student.

UNPIVOT Operator in SQL Server

Now let’s see how UNPIVOT Operator works in SQL Server.

UNPIVOT Operators in SQL Server work exactly the reverse of PIVOT. It converts data from column level to row level.

Let’s create the same dataset that we got after applying the PIVOT operator and understand how the UNPIVOT operator works in SQL Server.

CREATE TABLE StudentsReportUnpivot
(
StudentName VARCHAR(50),
English VARCHAR(50),
Mathematics VARCHAR(50),
Science VARCHAR(50),
History VARCHAR(50)
)

INSERT INTO StudentsReportUnpivot VALUES('David', 90, 98, 98, 100)
INSERT INTO StudentsReportUnpivot VALUES('Robert', 95, 98, 90, 90)

Now, let’s fetch the data using a SELECT query.

SELECT * FROM StudentsReportUnpivot;

The dataset looks the same as that we had created after applying the PIVOT operator.

Now, let’s run the below query to see how the UNPIVOT operator works in the dataset StudentsReportUnpivot.

SELECT StudentName, Subjects, Marks FROM 
	(SELECT StudentName, English, Mathematics, Science, History FROM StudentsReportUnpivot) Table1
UNPIVOT 
	(Marks FOR Subjects IN (English, Mathematics, Science, History)) AS Table2

In the above dataset, we can see that the UNPIVOT Operator works exactly the reverse of PIVOT. It converts all data from the Column level to the row level.

Conclusion

In this article, we have understood PIVOT and UNPIVOT operators in SQL Server and have also learned how to apply them. I hope you liked the article. Please share your suggestions and feedback in the comment section.