Pivot Operator in SQL Server

The pivot operator in Microsoft SQL Server turns each row in the aggregated result set into a corresponding column in the output set. Use the SQL Pivot table operator to create tables containing all the rows of a query, sorted in order of importance.

Preparing the Data

We’ll start with some dummy data that we can use to implement the pivot operator.

CREATE DATABASE schooldb					
CREATE TABLE student
(
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    gender VARCHAR(50) NOT NULL,
    DOB datetime NOT NULL,
    total_score INT NOT NULL,
    city VARCHAR(50) NOT NULL
 )
INSERT INTO student
VALUES (1, 'Jolly', 'Female', '12-JUN-1989', 500, 'London'), 
(2, 'Jon', 'Male', '02-FEB-1974', 545, 'Manchester'), 
(3, 'Sara', 'Female', '07-MAR-1988', 600, 'Leeds'), 
(4, 'Laura', 'Female', '22-DEC-1981', 400, 'Liverpool'), 
(5, 'Alan', 'Male', '29-JUL-1993', 500, 'London'), 
(6, 'Kate', 'Female', '03-JAN-1985', 500, 'Liverpool'), 
(7, 'Joseph', 'Male', '09-APR-1982', 643, 'London'), 
(8, 'Mice', 'Male', '16-AUG-1974', 543, 'Liverpool'), 
(9, 'Wise', 'Male', '11-NOV-1987', 499, 'Manchester'), 
(10, 'Elis', 'Female', '28-OCT-1990', 400, 'Leeds');

How Pivot Operators Work?

The standard way of grouping SQL data is to use the Group By clause. Here’s the query you need to write that calculates the average of the values in the total_score column of the student table, grouped by city.

USE schooldb
SELECT city, AVG(total_score) as Avg_Score
FROM student
GROUP BY city

Result:

City	Avg_Score
Liverpool	481
Leeds	500
Manchester	522
London	547

Imagine if we wanted to display each student’s scores on the class, and also displayed the cities in columns for each score, so that for example the students from each of those three cities had their individual columns of data next:

London	Liverpool	Leeds	Manchester
547	481	500	522

Base Data

To use the pivot operator you first need to select the base data that the pivot operator will be based on. We want to get the total_score for each student in that city. Therefore, we need to write a simple SELECT statement that selects the city, total_score, and player.

SELECT city, total_score
FROM student

Temporary Dataset

Unfortunately, we don’t have direct access to pivot the data. We would need to create a new column that we could use as the pivot column. In order for the pivot operator to work, we need to create a table-valued expression that we can apply the pivot operator to. We have a variety of choices here – we could use derived tables, common table expressions (CTEs), we could create temporary tables, or even use an array.

For this example, we’ll use a derived table.

A statement (the SELECT statement) is similar to a formula, and can be used to build more complex queries that perform complex tasks. Finally, we select all of that data from that derived table.

SELECT * FROM
(SELECT city, total_score
FROM student) AS StudentTable

Pivot Operator

Next, we’ll prepare our base data and create a derived table. Then, we’ll apply the pivot operator to it. To create the pivot table that looks for “PIVOT” followed by parentheses, and then assigns it an alias. We need to include the parenthesis in our sentence to make it clear to the readers what is inside the parentheses.

We need to specify the field(s) we want to apply an aggregate function to. Our case is that we want to find out the average of a score given by the AVG aggregate function in the
After we do that, we will want to pivot the data so that it’s the columns that are grouped together. This is done by writing “FOR” followed by the column name which is a city in our example
This process will leave us with a messy looking pivot table. But, we can fix it by putting in some headers. Parentheses are used when you want to match a particular pattern. In the parenthesis, we use a comma-separated list where we write the name of each column inside a square bracket. In this example, I want London, Leeds, and Manchester as the headers of the pivot table. I write them in this format: ([London], [Leeds], [Manchester]).

USE schooldb
SELECT * FROM
(SELECT city, total_score
FROM student)
AS StudentTable
PIVOT 
( 
AVG(total_score)
FOR city IN ([London],[Liverpool],[Leeds],[Manchester])
) 
AS StudentPivotTable

Result:

London	Liverpool	Leeds	Manchester
547	481	500	522

Row Groups in Pivot Table

In the previous sections, we saw how to convert row groups into column groups by using the pivot operator. However, you can also add row groups and column groups into a pivot table.

For example, if you want to know the average value of total_score for every student within a specific city, you can use the group function of the total_score column and the city column in conjunction inside a pivot table.

Each column will represent a student gender, and each row will represent a city. In order to create column groups in a pivot table, you don’t need to write any additional code. The base dataset will include a list of columns. You can then add a column to the data set by adding a row group using a column that you want to appear in the pivot table as the row group.

USE schooldb
SELECT * FROM
(SELECT city, gender, total_score
FROM student)
AS StudentTable
PIVOT
( 
AVG(total_score)
FOR city IN ([London],[Liverpool],[Leeds],[Manchester])
) 
AS StudentPivotTable

Let’s take a look at an example where gender is added to the SELECT statement. The output of the above query looks like this:

Gender	London	Liverpool	Leeds	Manchester
Female	500	450	500	NULL
Male	571	543	NULL	522

This is a cross-tabulation and it shows that the average score of total students who live in London is 500. The average total score for male students living in London is higher than that for students living in other regions of the UK.