SQL Server: PIVOT Clause

In this post explains how to use the PIVOT clause in SQL Server (Transact-SQL) with syntax and examples.

Description

The SQL Server (Transact-SQL) PIVOT clause allows you to write a cross-tabulation. This means that you can aggregate your results and rotate rows into columns.

Syntax

The syntax for the PIVOT clause in SQL Server (Transact-SQL) is:

SELECT first_column AS first_column_alias>,
[pivot_value1], [pivot_value2], ... [pivot_value_n]
FROM 
(source_table>) AS source_table_alias>
PIVOT 
(
 aggregate_function(aggregate_column>)
 FOR pivot_column>
 IN ([pivot_value1], [pivot_value2], ... [pivot_value_n])
) AS pivot_table_alias>;

Parameters or Arguments

first_column

A column or expression that will display as the first column in the pivot table.

first_column_alias

The column heading for the first column in the pivot table.

pivot_value1, pivot_value2, ... pivot_value_n

A list of values to pivot.

source_table

A SELECT statement that provides the source data for the pivot table.

source_table_alias

An alias for source_table.

aggregate_function

An aggregate function such as SUM, COUNT, MIN, MAX, or AVG.

aggregate_column

The column or expression that will be used with the aggregate_function.

pivot_column

The column that contains the pivot values.

pivot_table_alias

An alias for the pivot table.

Applies To

The PIVOT clause can be used in the following versions of SQL Server (Transact-SQL):

• SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

DDL/DML for Examples

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!

Get DDL/DML

Example

The PIVOT clause can be used in SQL Server (Transact-SQL).

Let's look at an example. If we had an employees table that contained the following data:

And we ran the following SQL statement which creates a cross-tabulation query using the PIVOT clause:

SELECT 'TotalSalary' AS TotalSalaryByDept, 
[30], [45]
FROM
(SELECT dept_id, salary
 FROM employees) AS SourceTable
PIVOT
(
 SUM(salary)
 FOR dept_id IN ([30], [45])
) AS PivotTable;

It would return the following result:

This example would create a pivot table to display the total salary for dept_id 30 and dept_id 45. The results are displayed in one row with the two departments appearing each in their own column.

Now, let's break apart the PIVOT clause and explain how it worked.

Specify Columns in Cross-Tabulation Results

First, we want to specify what fields to include in our cross tabulation results. In this example, we want to include the literal value 'TotalSalary' as the first column in the pivot table. And we want to create one column for dept_id 30 and a second column for dept_id 45. This gives us 3 columns in our pivot table.

SELECT 'TotalSalary' AS TotalSalaryByDept, 
[30], [45]

Specify the Source Table Data

Next, we need to specify a SELECT statement that will return the source data for the pivot table.

In this example, we want to return the dept_id and salary values from the employees table:

(SELECT dept_id, salary
 FROM employees) AS SourceTable

You must specify an alias for the source query. In this example, we have aliased the query as SourceTable.

Specify Aggregate Function

Next, we need to specify what aggregate function to use when creating our cross-tabulation query. You can use any aggregate such as SUM, COUNT, MIN, MAX, or AVG functions.

In this example, we are going to use the SUM function. This will sum the salary values:

PIVOT
(SUM(salary)

Specify Pivot Values

Finally, we need to specify what pivot values to include in our results. These will be used as the column headings in our cross-tabulation query.

In this example, we are going to return only the dept_id values of 30 and 45. These values will become our column headings in our pivot table. Also, note that these values are a finite list of the dept_id values and will not necessarily contain all possible values.

FOR dept_id IN ([30], [45])

Now when we put it all together, we get the following pivot table: