SQL Server PIVOT

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.

  1. The outer SELECT query (i.e. SELECT * FROM)
  2. The inner SELECT query within the outer SELECT which returns the base data from the table used by the rest of the query.
  3. 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_idcategory_name
1Mobile
2Headphone
3Tablet
4Laptop
Table: categories
product_idcategory_idproduct_namerelease_date
10272Bose Noise Cancelling Headphone 7005/13/2019
10282Sennheiser HD 450BT2/4/2020
10292Sony WH-1000XM38/15/2018
10302SoundMagic ES181/1/2017
10211Apple iPhone 11 Pro9/20/2019
10221Samsung Galaxy Note 10 Plus8/23/2019
10353Samsung Galaxy Tab S610/11/2019
10363Microsoft Surface Pro6/15/2017
10373iPad Air3/18/2019
10383Lenovo Tab M88/8/2019
10393Dell Venue 71/4/2014
10403HP 7 VoiceTab10/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_nameproduct_count
Headphone4
Mobile2
Tablet6

Now suppose we want the above data in the below format with the number of products under each category.

HeadphoneMobileTablet
426

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_nameproduct_id
Headphone1027
Headphone1028
Headphone1029
Headphone1030
Mobile1021
Mobile1022
Tablet1035
Tablet1036
Tablet1037
Tablet1038
Tablet1039
Tablet1040

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.

MobileHeadphoneTablet
246

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_yearMobileHeadphoneTablet
2014002
2017011
2018010
2019213
2020010

Advertisement