SQL Server PIVOT and UNPIVOT Explained with Examples

In this article, you’ll learn:

• what the PIVOT feature is in SQL Server
• a few examples of using PIVOT
• how to use the UNPIVOT feature

Sometimes you’ll need to pivot your data if it’s stored in a different way to how you’d like it displayed.

You can use the SQL Server PIVOT feature to do that.

Let’s get into it!

What is PIVOT?

The PIVOT feature or concept in SQL databases allows you to change the data from being displayed in rows to columns.

It groups your data based on some values and shows these in columns instead.

If you’ve ever created a Pivot Table in Excel and want to have a pivot table in SQL, then the Pivot feature is how you do it.

 

Sample Data and Expected Output

Let’s see some sample data and our expected output to help demonstrate what PIVOT can do.

We’ve got a product_sales table which has a product name, store location, and a number of sales. Each of these attributes are in separate columns:

product_name	store_location	num_sales
Chair	North	55
Desk	Central	120
Couch	Central	78
Chair	South	23
Chair	South	10
Chair	North	98
Desk	West	61
Couch	North	180
Chair	South	14
Desk	North	45
Chair	North	87
Chair	Central	34
Desk	South	42
Couch	West	58
Couch	Central	27
Chair	South	91
Chair	West	82
Chair	North	37
Desk	North	68
Couch	Central	54
Chair	South	81
Desk	North	25
Chair	North	46
Chair	Central	121
Desk	South	85
Couch	North	43
Desk	West	10
Chair	North	5
Chair	Central	16
Desk	South	9
Couch	West	22
Couch	Central	59
Chair	South	76
Chair	West	48
Chair	North	19
Desk	North	3
Couch	West	63
Chair	South	81
Desk	North	85
Chair	North	90
Chair	Central	47
Desk	West	63
Couch	North	28

Here’s the sample data for this table: sql_server_pivot_data.sql

Let’s say we wanted to see this data with product names on the left and store locations across the top, with the number of sales at each intersection:

product_name	North	Central	South	West
Chair	437	218	376	130
Couch	251	218	0	143
Desk	226	120	136	134

We could extract it into an Excel file and create a pivot table.

We could use a bunch of CASE statements to display the data in a pivot table, but this can get long and messy.

Or, we could use the SQL Server PIVOT feature.

 

SQL Server PIVOT Feature

There’s a PIVOT keyword in SQL Server. This lets you transpose data in rows into column headers. The data is aggregated to meet the required conditions and displayed in the output.

The syntax looks like this:

SELECT non_pivoted_column,
first_pivoted_column AS column_name,
…
last_pivoted_column AS column_name
FROM (
  SELECT query that produces data
) AS alias_for_select
PIVOT (
  aggregate_function (aggregate_column)
  FOR [column_with_header_values]
  IN (first_pivoted_column, … last_pivoted_column)
) AS alias_for_pivot_table;

There is a lot here. Let’s explain it:

• non_pivoted_column: This is the column to show the values in the first column of output.
• first_pivoted_column: This is the first column that shows pivoted data.
• last_pivoted_column: Keep adding as many columns as you want for your pivot.
• query that produces data: This SELECT query is the one that produces the data that drives the pivot result. It will select data from your tables.
• alias_for_select: An alias for the SELECT subquery is needed here.
• aggregate_function: The aggregate function to use for the combination of pivot column and row, such as AVG or SUM or COUNT.
• aggregate_column: The column to perform the aggregate function on.
• column_with_header_values: This is the column that has all of the values to use for each of the headers.
• alias_for_pivot_table: An alias for the pivot table is needed.

If that seems confusing so far, that’s OK. It’s easier to understand with an example.

 

Pivot Example

We’ve got our data in our product_sales table here:

product_name	store_location	num_sales
Chair	North	55
Desk	Central	120
Couch	Central	78
Chair	South	23
…

How can we write a pivot table query?

First, we write a SELECT query that gets the data we need for the pivot table.

SELECT product_name, store_location, num_sales
FROM product_sales

Then, we contain this in a subquery:

SELECT
 
FROM (
  SELECT product_name, store_location, num_sales
  FROM product_sales
) AS alias_for_select

Then we need to specify the column that has the data that goes on the left column. In this case, we want to show the product names on the left and locations on the top, so we’ll add the product_name column to the outer SELECT clause.

SELECT
product_name
 
FROM (
  SELECT product_name, store_location, num_sales
  FROM product_sales
) AS alias_for_select

Now it’s time to add the pivot.

Add the word PIVOT after the alias, at the end of the query. Add open and closing brackets, then an alias for it (such as pivot_table).

SELECT
product_name
 
FROM (
  SELECT product_name, store_location, num_sales
  FROM product_sales
) AS alias_for_select
PIVOT
(
 
) AS pivot_table;

Now, inside the brackets of the PIVOT clause, we add the aggregate function we want to use. We want to see the “number of sales in each store location”, and the num_sales field has this number, and we want to SUM these values. So, we use the SUM function.

SELECT
product_name
 
FROM (
  SELECT product_name, store_location, num_sales
  FROM product_sales
) AS alias_for_select
PIVOT
(
  SUM(num_sales)
 
) AS pivot_table;

Then, we add the word FOR, then column that has the different values we want across the top, for our columns.

SELECT
product_name
 
FROM (
  SELECT product_name, store_location, num_sales
  FROM product_sales
) AS alias_for_select
PIVOT
(
  SUM(num_sales)
  FOR store_location
) AS pivot_table;

After the FOR store_location we add the word IN, then we specify the different values to show in the columns.

This will let the PIVOT clause calculate the SUM for each of the values.

Yes, we need to specify the actual column names. We’ll see how we can do this dynamically later in this guide.

SELECT
product_name
 
FROM (
  SELECT product_name, store_location, num_sales
  FROM product_sales
) AS alias_for_select
PIVOT
(
  SUM(num_sales)
  FOR store_location IN (North, Central, South, West)
) AS pivot_table;

Notice that the column names in this FOR clause are not contained in single quotes. It may seem like we need to, because they are values in the store_location column, but we don’t need single quotes. We don’t even need square brackets.

Finally, we add these specific column headings from the FOR clause into our SELECT clause at the top, so we can see the calculated values.

SELECT
product_name,
North, Central, South, West
FROM (
  SELECT product_name, store_location, num_sales
  FROM product_sales
) AS alias_for_select
PIVOT
(
  SUM(num_sales)
  FOR store_location IN (North, Central, South, West)
) AS pivot_table;

We don’t need single quotes or square brackets in the SELECT clause either.

Now you should be able to run this query and get a pivot table result.

Here’s what the result will show:

product_name	North	Central	South	West
Chair	437	218	376	130
Couch	251	218	NULL	143
Desk	226	120	136	134

We can see the different locations as columns, the different products as rows, and the sum of sales at the intersection of product and location.

That’s how you can do a pivot table in SQL Server. You can change the columns and rows to use by changing your query, but we’ve seen the general structure here.

 

Dynamic PIVOT Columns

In the earlier example, we generated a pivot table. However, we needed to specify each of the column headings for it to work.

This may be OK for smaller results, but what if you don’t know all of the possible values? Or what if the values change?

There is a way to dynamically generate the columns of a PIVOT table output. This query was adapted from the top answer to this StackOverflow question.

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);
 
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(store_location) 
            FROM product_sales
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');
 
SET @query = 'SELECT product_name, ' + @cols + ' FROM 
            (
                SELECT product_name,
                     store_location,
                     num_sales
                FROM product_sales
           ) AS alias_for_select
            PIVOT
            (
                 SUM(num_sales)
                FOR store_location IN (' + @cols + ')
            ) AS pivot_table ';
 
EXECUTE(@query);

What does this code do?

• We declare two variables: one to contain column values, and another to contain the query.
• We set the cols variable to the result of the STUFF function. This function will get the distinct store_location values from the product sales and convert them into the format needed for the PIVOT clause below.
• We then set the query variable to the SELECT query that does the pivot. This is almost the same as the earlier example, with the only difference being the usage of the cols variable instead of specifying the values of North, South, Central, and West.
• The Execute command will run the query variable as a query.

You should get the same result as above:

product_name	North	Central	South	West
Chair	437	218	376	130
Couch	251	218	NULL	143
Desk	226	120	136	134

With this example, your column headers are generated based on the values in the table.

 

SQL Server UNPIVOT

SQL Server also offers an UNPIVOT feature. This is almost the reverse of the PIVOT feature. The UNPIVOT feature will rotate columns into rows.

For our example, let’s say we had the output of the previous table inserted into a database table.

product_name	north	central	south	west
Chair	437	218	376	130
Couch	251	218	NULL	143
Desk	226	120	136	134

Here’s the SQL to do that.

CREATE TABLE product_sales_pivoted (
    product_name VARCHAR(100),
    north INT,
    central INT,
    south INT,
    west INT
);
 
INSERT INTO product_sales_pivoted (product_name, north, central, south, west) VALUES
('Chair', 437, 218, 376, 130),
('Couch', 251, 218, NULL, 143),
('Desk', 226, 120, 136, 134);

Let’s say we wanted to rotate this table and show the following columns:

• product_name
• store_location: either north, central, south, or west
• num_sales

Here’s the SQL to do that:

SELECT product_name, store_location, num_sales
FROM (
  SELECT product_name, north, central, south, west
  FROM product_sales_pivoted
) AS pivoted_table
UNPIVOT 
(
    num_sales FOR store_location IN (north, central, south, west)
) AS unpivot_table;

We can see it’s similar to the PIVOT query. We specify the values to use for each of the store_location rows in two places: the inner SELECT query and the UNPIVOT clause.

Here are the results of this query.

product_name	store_location	num_sales
Chair	north	437
Chair	central	218
Chair	south	376
Chair	west	130
Couch	north	251
Couch	central	218
Couch	west	143
Desk	north	226
Desk	central	120
Desk	south	136
Desk	west	134

We can see columns of product_name, store_location, and num_sales.

It’s not an exact reversal of the PIVOT function, because the pivoted data is aggregated, and UNPIVOT does not deaggregate the data. There is one row in this result set for each combination of product_name and store_location.

Also, there are no NULL values here. The NULL value for the product Couch in location West is not in the output here.

Conclusion

The SQL Server PIVOT and UNPIVOT features are a handy way to easily change the way you see your results. The columns in the PIVOT table can be specified, or you can write a little more code to get them to dynamically appear.