Learning Objective
The objective of this SQL Server tutorial is to teach you how to use the PIVOT operator to format the output of a SQL query around the values of a table column.
What is PIVOT in SQL Server?
PIVOT literally means the central point around which something revolves. The PIVOT operator in SQL is so called because it acts as a PIVOT (i.e. a central point) to organize query resultset. It is a tool used to format the output of a query resultset so that it can presented in a spreadsheet form for reporting purposes. It is used in SELECT statements to format the output around the values of a particular column or field.
A query resultset is in the form of a table with rows and columns. The PIVOT operator uses the data values of a column called the PIVOT column and turns the table around it. As a result, those data values become the columns (or column headings) in the rotated table. The columns created by PIVOT hold aggregate values. Any other column specified in the SELECT query form row groups in the pivot output.
SQL Server PIVOT Syntax
The basic syntax of the PIVOT operator can be represented as below.
SELECT * FROM ( SELECT pivot_column, additional_column, aggregate_function (column) FROM table1 alias1 INNER JOIN table2 alias2 ON alias2.common_column = alias1.common_column ) alias3 PIVOT ( aggregate (column) FOR pivot_column IN ( value1, value2, ……………, valueN ) ) alias4;
The query above can be divided into 3 parts.
- The outer SELECT query (i.e. SELECT * FROM)
- The inner SELECT query within the outer SELECT which returns the base data from the table used by the rest of the query.
- The PIVOT clause which maps the aggregated values of the column chosen for aggregation against the values of the pivoted column.
In this syntax,
- pivot_column – the column on which the PIVOT operation is performed.
- additional_column – any column in the SELECT list apart from the column which is pivoted and the column which is aggregated.
- aggregate_function (column) – column on which aggregate function (i.e. SUM, COUNT, AVG etc.) is used.
- table1 – the first table in the JOIN from which all or most of the final resultset values come.
- table2 – the second table in the JOIN
- alias1/alias2 – table alias names for table1 and table2.
- alias3 – table alias for the inner SELECT query which acts as a base table for the outer SELECT query.
- alias4 – alias for the output of the PIVOT operation.
- PIVOT – SQL operator for pivoting a column making the column values containers holding aggregate values.
- value –static data values of the pivoted column.
SQL Server PIVOT with Example
We will try to understand the above PIVOT query and operation with the help of an example. Let us consider two tables – categories and products. The categories table contains some category names to which the products belong and the products table contains the names of products belonging to one or another category. The tables are represented below. We will use them as our reference for the examples.
| category_id | category_name |
| 1 | Mobile |
| 2 | Headphone |
| 3 | Tablet |
| 4 | Laptop |
| product_id | category_id | product_name | release_date |
| 1027 | 2 | Bose Noise Cancelling Headphone 700 | 5/13/2019 |
| 1028 | 2 | Sennheiser HD 450BT | 2/4/2020 |
| 1029 | 2 | Sony WH-1000XM3 | 8/15/2018 |
| 1030 | 2 | SoundMagic ES18 | 1/1/2017 |
| 1021 | 1 | Apple iPhone 11 Pro | 9/20/2019 |
| 1022 | 1 | Samsung Galaxy Note 10 Plus | 8/23/2019 |
| 1035 | 3 | Samsung Galaxy Tab S6 | 10/11/2019 |
| 1036 | 3 | Microsoft Surface Pro | 6/15/2017 |
| 1037 | 3 | iPad Air | 3/18/2019 |
| 1038 | 3 | Lenovo Tab M8 | 8/8/2019 |
| 1039 | 3 | Dell Venue 7 | 1/4/2014 |
| 1040 | 3 | HP 7 VoiceTab | 10/23/2014 |
Suppose we want to get the count of products belonging to the different categories i.e. we want to find out how many types of mobiles, how many types of headphones do we have in our stock for sale. We can do so with the help of the following query which uses the GROUP BY function on the category_name column and count aggregate function on the product_id column. The INNER JOIN retrieves the values of the category_name column from the categories table. By doing so it determines the number of products available for each category. p and c are table aliases for products and categories tables and product_count is the column alias for the aggregated product_id column.
SELECT category_name, COUNT (product_id) product_count FROM products p INNER JOIN categories c ON c.category_id = p.category_id GROUP BY category_name;
The resultset of the above query is the following.
| category_name | product_count |
| Headphone | 4 |
| Mobile | 2 |
| Tablet | 6 |
Now suppose we want the above data in the below format with the number of products under each category.
| Headphone | Mobile | Tablet |
| 4 | 2 | 6 |
We can do so with the help of the PIVOT operator. The PIVOT operator as mentioned in the introduction will turn the table around on the category_name column value (i.e. Headphone, Mobile, Tablet) so that it can hold the count of products (4, 2, 6) in each category. But to do so we have to first fetch the required data (called base data) from the tables using SELECT query and store it in a temporary table (called derived table). Then we can then apply the PIVOT operator on it. The following SELECT query fetches the base data and stores it in a temporary table called temp_table.
SELECT * FROM ( SELECT category_name, product_id FROM products p INNER JOIN categories c ON c.category_id = p.category_id ) temp_table;
The resultset of the above query is the following. We have not taken the count of the product_id here but just extracted the relevant data from the tables and stored it in the derived table temp_table.
| category_name | product_id |
| Headphone | 1027 |
| Headphone | 1028 |
| Headphone | 1029 |
| Headphone | 1030 |
| Mobile | 1021 |
| Mobile | 1022 |
| Tablet | 1035 |
| Tablet | 1036 |
| Tablet | 1037 |
| Tablet | 1038 |
| Tablet | 1039 |
| Tablet | 1040 |
Now we will apply the PIVOT operator on the derived table which contains the above resultset to format the resultset as we want it. The following query does the same. The first part of the query is the same above query (which has already been shown separately for understanding but is an integral part of the below complete query). The second part of the query is the PIVOT which gets the product_id’s from the temp_table, takes their count and maps them to the static category_names provided explicitly with the FOR clause.
SELECT * FROM ( SELECT category_name, product_id FROM products p INNER JOIN categories c ON c.category_id = p.category_id ) temp_table PIVOT ( COUNT (product_id) FOR category_name IN ( Mobile, Headphone, Tablet) ) pivot_table;
The above query produces the is the following pivoted resultset which is what we wanted.
| Mobile | Headphone | Tablet |
| 2 | 4 | 6 |
So this is the way PIVOT works. We can improvise the above query by adding one more column in the SELECT list. The below query adds the year from the release_date column using the year () function to the SELECT list. The rest of the query is the same.
SELECT * FROM ( SELECT category_name, product_id, year(release_date) release_year FROM products p INNER JOIN categories c ON c.category_id = p.category_id ) temp_table PIVOT ( COUNT (product_id) FOR category_name IN ( Mobile, Headphone, Tablet) ) pivot_table;
The query produces the following resultset. As we can see the additional column (aliased release_year in the query) has been has been taken as another column and its values as row values and the product count distributed category and release year wise.
| release_year | Mobile | Headphone | Tablet |
| 2014 | 0 | 0 | 2 |
| 2017 | 0 | 1 | 1 |
| 2018 | 0 | 1 | 0 |
| 2019 | 2 | 1 | 3 |
| 2020 | 0 | 1 | 0 |