The pivot operator in SQL Server converts each row in the aggregated result set into corresponding columns in the output set. The pivot operator is particularly useful in writing cross-tabulation queries.
Let’s take a look at how it works in practice.
Preparing the Data
First, let’s create some dummy data which we can then 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 Does The Pivot Operator Work?
The standard way of grouping SQL data is by using the Group By clause. Let’s create a query 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
This gives the following result:
[table id=25 /]
What if we want a result set where the city names are displayed in columns where each column contains the average value of the total_score of the students belonging to that city? Something like this:
[table id=26 /]
This is where the pivot operator comes handy.
Selecting the Base Data
The first step when using the pivot operator is to select the base data that the pivot operator will be based on. We want to group our data by city and find the average of the total_score of the students who belong to that city. Therefore, we need to write a simple SELECT statement that selects the city and total_score.
SELECT city, total_score FROM student
Creating a Temporary Dataset
Now, ideally, we would be able to directly apply the pivot operator on the base data that we created in the previous section but unfortunately, we can’t. In order for the pivot operator to work, we have 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) or we could even create temporary tables.
For this example, we will use a quick, simple derived table. To do that with the basic select statement that we created in the last section, we wrap it in a set of parenthesis and then apply an alias to it. Finally, we select everything from that derived table.
SELECT * FROM (SELECT city, total_score FROM student ) AS StudentTable
Applying the Pivot Operator
Now that we have prepared our base data and have created a derived table, we will apply the pivot operator to it.
To do this, insert “PIVOT” at the end of the derived table, followed by a set of parenthesis, and give this pivot table an alias.
Inside the parenthesis, we have to specify some important information.
- We need to specify the field we want to apply an aggregate function to. In our case, we want to apply the AVG aggregate function in the “total_score” column.
- We then have to say which columns from the base data we are pivoting our data onto. We do so by writing “FOR” followed by the column name which is a city in our example.
- The final step is a little irritating. We have to list the values from the city column that we want to become headings in our pivot table. We use the IN operator followed by a set of parenthesis. Inside the parenthesis, we use a comma-separated list where we write the name of each column inside a square bracket. In our example, we want London, Leeds, and Manchester as the heading names of the pivot table and so we 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
If you execute the above query, the results will look like this:
[table id=27 /]
Adding Row Groups in Pivot Table
In the previous sections, we saw how to convert row groups into column groups using pivot operator. However, you can also add row groups along with column groups into a pivot table.
For example, if you want to find the average value of the total_score column of all the students grouped by the city as well as by gender you can use the column group and the row group in conjunction inside a pivot table. Here each column will represent a city name and each row will represent a student gender.
Luckily, you don’t have to write any additional script to add row groups to a pivot table. Inside the base dataset, simply add the column name which you want to add as a row group to the pivot table.
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
In the above script, we simply added “gender” column in the base SELECT statement.
The output of the above query looks like this:
[table id=28 /]
This is cross-tabulation. For instance, it can be seen from the results that the average total_score of female students living in London is 500. Similarly, average total score of male students living in London is 571.