SQL Server PIVOT Operator Explained

1. Introduction

SQL Server has supported the PIVOT operator since version 2005. It is used to generate a multidimensional report by transferring data from the row level to the column level.

PIVOT Syntax

SELECT <non-pivoted column>,  
    [first pivoted column] AS <column name>,  
    [second pivoted column] AS <column name>,  
    ...  
    [last pivoted column] AS <column name>  
FROM  
    (<SELECT query that produces the data>)   
    AS <alias for the source query>  
PIVOT  
(  
    <aggregation function>(<column being aggregated>)  
FOR   
[<column that contains the values that will become column headers>]   
    IN ( [first pivoted column], [second pivoted column],  
    ... [last pivoted column])  
) AS <alias for the pivot table>  
<optional ORDER BY clause>

In this example, I will prepare two tables along with sample data and demonstrate the Pivot operator:

• Pivot at category_id from the products table.
• Pivot at category_name from the products‘s join table categories.
• Pivot at category_name to generate a two-dimensional report.

2. Technologies Used

The example code in this article was built and run using:

• SQL Server
• SQuirrel Client 3.9.0
• SQL

3. Setup Data

In this step, I will create two database tables:

• categories – has two columns: category_id and category_name. It has three records.
• products – has four columns: product_id, category_id, product_name, and release_date. It has eight records.

setupdata.sql

create table categories (
category_id  INT,
category_name VARCHAR(40)
);

create table products (
product_id INT,
category_id  INT,
product_name VARCHAR(40),
release_date DATE
);

insert into categories ( category_id  , category_name ) values(1, 'Mobile');
insert into categories ( category_id  , category_name ) values(2, 'Headphone');
insert into categories ( category_id  , category_name ) values(3, 'Tablet');

insert into products ( product_id , category_id  , product_name, release_date ) values(1027,2, 'Headphone 700', '5/13/2019');
insert into products ( product_id , category_id  , product_name, release_date ) values(1028,2, 'Headphone 450BT', '2/4/2020');
insert into products ( product_id , category_id  , product_name, release_date ) values(1029,2, 'HD 1000XM3', '8/5/2018');
insert into products ( product_id , category_id  , product_name, release_date ) values(1030,2, 'HD SoundES18', '1/1/2017');
insert into products ( product_id , category_id  , product_name, release_date ) values(1021,1, 'APPLE 700', '9/20/2019');
insert into products ( product_id , category_id  , product_name, release_date ) values(1022,1, 'Samsung 5', '8/23/2019');
insert into products ( product_id , category_id  , product_name, release_date ) values(1035,3, 'GalatS6', '10/13/2019');
insert into products ( product_id , category_id  , product_name, release_date ) values(1036,3, 'MS', '6/13/2017');

Checking products table data with a select statement.

select statement

select * from products;

Products records

3.1 Group By Category_id

In this step, I will query the total product counts based on the category_id.

group by category_id

select category_id, count(*) totalProductCount from products group by category_id;

Note: the group by column: category_id is the PIVOT column that will be used at step 4.1

group by category_id output

There are two products with category_id of 1, four products with category_id of 2, and 2 products with category_id of 3.

In SQuirrel client tool, clicking the fifth tab – Rotated Table, then it rotates the three rows under the category_id column into three columns.

Rotated Table Results

The three columns: Row_1, Row_2, and Row_3 are converted from the category_id row values. In step 4.2, I will show how to use the PIVOT operator to achieve the same rotated results.

3.2 Group By with Where Condition

In this step, I limit the query results from step 3.1 by adding a where clause when querying the product counts based on category_id.

Group with Where Clause

select category_id, count(*) productCountsByCat_2019 from products where release_date between '2019-01-01' and '2019-12-31' group by category_id;

Group By with Where Clause output

The product counts based on the category_id are limited to the products released in 2019.

Using the SQuirrel Client tool, click the fifth tab – Rotated Table. It rotated the three rows under the category_id column into three columns.

Rotated table with where clause

The rotated columns are from the category_id values. The row productCountsByCat_2019 shows the products released in the year 2019. In step 4.3, I will show you how to use the PIVOT operator to achieve the same rotated results by generating a multidimensional report for all release years.

4. SQL Server PIVOT Operator Examples

In this step, I will show three examples that pivot at either category_id or category_name.

4.1 PIVOT on Category_id

In this step, I will use the PIVOT operator to show the product count for each category_id. It should have the same results as step 3.1 after clicking the Rotated Table tab. The pivot_column is the category_id column from the products table. The aggregation function is the count(product_id) function.

Pivot by Category_id

SELECT *
FROM   (SELECT category_id,
               product_id
        FROM   products p) temp_table
       PIVOT ( Count (product_id)
             FOR category_id IN ( [1],
                                  [2],
                                  [3]) ) pivot_table; 

Query Results

The column names are converted from the 3-row values from step 3.1 which are outlined in the query at lines 6, 7, and 8.

4.2 PIVOT on Category_name

In this step, I will alter the query in step 4.1 to show the category_name instead of category_id for better readability.

Pivot by Category_name

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; 

Query Results

Here, it has similar data as table 4 but more descriptive column names at lines 8, 9, and 10.

4.3 PIVOT on Category_name with Release_date

In this step, I will generate a two-dimension product count report. It’s based on release_year and category_name.

Pivot on Category_name with Release Year

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; 

Two-dimensional Report

As you can see, the 2-dimensional product count report is based on the category_name and release_year. It is generated with a PIVOT operator and shows the total product count by category_name for each release year.

5. Summary

In this example, I explained the PIVOT operator with sample data. The Pivot operator is similar to the Group By clause but has better readability for reporting.

6. Download the Source Code

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

I agree to the Terms and Privacy Policy

Sign up

Thank you!

We will contact you soon.