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
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:
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
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
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
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:
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.