sql

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

product_idcategory_idproduct_namerelease_date
10272Headphone 7005/13/2019
10282Headphone 450BT2/4/2020
10292HD 1000XM38/5/2018
10302HD SoundES181/1/2017
10211APPLE 7009/20/2019
10221Samsung 58/23/2019
10353GalatS610/13/2019
10363MS6/13/2017
Table 1 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

category_idtotalProductCount
12
24
32
Table 2 Total Product Count Group by Category_id

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

sql pivot - category id
Figure 1 Rotated Table – Category_id

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

category_idproductCountsByCat_2019
12
21
31
Table 3 Product Counts By Category_id in 2019

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

sql pivot - by year
Figure 2 Rotated Table By Year

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

123
242
Table 4 PIVOT by category_id

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

mobileheadphonetablet
242
Table 5 Pivot by category_name

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

release_yearmobileheadphonetablet
2017011
2018010
2019211
2020010
Table 6 Product count reports by category_name and release year

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

Download
You can download the full source code of this example here: SQL Server PIVOT Operator Explained

Mary Zheng

Mary has graduated from Mechanical Engineering department at ShangHai JiaoTong University. She also holds a Master degree in Computer Science from Webster University. During her studies she has been involved with a large number of projects ranging from programming and software engineering. She works as a senior Software Engineer in the telecommunications sector where she acts as a leader and works with others to design, implement, and monitor the software solution.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button