The best way to achieve success is to have a central point or core around which everything else you do revolves. The PIVOT operator in SQL is so called because it acts as a pivot (i.e. PIVOT). A pivot is a query that turns rows into columns.
It’s an important task to organize a query result set. It’s a tool used to format the output of a query result set so that it can presented in a spreadsheet form for reporting purposes. SQL uses it to format the results of a query based on a particular column or field.
A SQL Server pivot table result set is in the form of a table with rows and columns. If you have a pivot table in Excel, you may use the PIVOT operator to turn the data around it. After the data has been entered, the rows of each table become the new headers or column headings. The PIVOT function returns an aggregate value for each unique combination of column values, so all other columns are aggregated.
Syntax
It’s a bit hard to explain in words, but the basic syntax of the PIVOT operator represented 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 divided into 3 parts:
- The outer SELECT query (i.e. SELECT * FROM)
- This is the inner SELECT query, used to calculate the base data required for the rest of the SELECT
- The PIVOT clause is used to map the values of the column chosen for aggregation to the 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. We'll consider two tables – categories and products. The categories and the products are related to each other through the product table. The products table contains the name of each product. 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 | 1 | 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 |
INNER JOIN Example
We want to find out how many products belong to the different categories i.e. and how many types of mobiles, how many types of headphones we have for sale in our stock.
You can filter this product list down using the following query which uses the GROUP BY function on the category_name column and the count aggregate function on the product_id column.
The INNER JOIN retrieves the values of the name column from the categories table, but only if the name column from the items table matches the name column from the categories table. The product and category tables will be aliased as p and c respectively and the product_count column will be aliased as product_count.
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;Result:
| category_name | product_count |
|---|---|
| Headphone | 4 |
| Mobile | 2 |
| Tablet | 6 |
The above data needs to be transformed into the below format:
| Headphone | Mobile | Tablet |
|---|---|---|
| 4 | 2 | 6 |
SELECT Example
We can do so using the PIVOT operator. It will turn the table around on the category_name column value (i.e. Headphone, Mobile, Tablet) so that it can count how many items in each category, and put them in the right order.
We need to fetch the data from the tables and store it in a temporary table first. Then, we'll filter it out. The PIVOT operator will change the values of the variables so that it can be rewritten as the following. In this SELECT query, the following statement retrieves the first name and last name from the Employees table, and stores them 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 result set on 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 |
FOR clause Example
We will now 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 second part of the query maps the product_id’s from the temp_table to the product_categories 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;In the query above, you see the following pivoted resultset which is what we wanted.
| Mobile | Headphone | Tablet |
|---|---|---|
| 2 | 4 | 6 |
When we use PIVOT, we can improvise it like this. Here, we add 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 output of the above query is given below. As we can see the category and the release year have been distributed into rows based on the product_count. We have selected product_count based on a specific condition where we need to select the number of products from each category for each year only.
| 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 |