Skip to main content

Chapter 8: Pivots

A pivot transforms rows into columns. Data stored as one row per category becomes one column per category. This is the difference between a normalized database format and a spreadsheet-style cross-tab report that analysts and dashboards typically need.

Before PIVOT (rows):          After PIVOT (columns):

category_name  | count        [Mountain] [Road] [Children] ...
---------------+------        ---------- ------ ----------
Mountain Bikes | 98           98         74     59
Road Bikes     | 74
Children       | 59

SQL Server provides the PIVOT operator to do this transformation inside a query.

8.1 How PIVOT Works​

The PIVOT operator takes three inputs:

  1. The aggregate function - what to compute for each cell (COUNT, SUM, AVG)
  2. The FOR column - which column's distinct values become the new column headers
  3. The IN list - the exact values to turn into columns (must be listed explicitly)
SELECT * FROM source_table
PIVOT (
    aggregate_function(column_to_aggregate)
    FOR column_with_pivot_values
    IN ([value1], [value2], [value3], ...)
) AS pivot_alias;

The values listed in IN must match exactly what is in the source column.

8.2 Three Steps to Build a Pivot Table​

The website's recommended approach: first build the base dataset, then wrap it, then apply PIVOT.

Source tables used throughout this chapter:

products and categories tables

Step 1 - Without PIVOT (standard GROUP BY):

The following query counts products per category the standard way:

SELECT
    category_name,
    COUNT(product_id) AS product_count
FROM production.products p
INNER JOIN production.categories c ON c.category_id = p.category_id
GROUP BY category_name;

GROUP BY result before pivot

Seven rows, one per category. The goal is to rotate this into seven columns on a single row.

Step 2 - Select the base dataset:

SELECT category_name, product_id
FROM production.products p
INNER JOIN production.categories c ON c.category_id = p.category_id

Only include the columns PIVOT needs: the column to become headers (category_name) and the column to aggregate (product_id).

Step 3 - Wrap in a derived table and apply PIVOT:

SELECT * FROM (
    SELECT category_name, product_id
    FROM production.products p
    INNER JOIN production.categories c ON c.category_id = p.category_id
) AS t
PIVOT (
    COUNT(product_id)
    FOR category_name IN (
        [Children Bicycles],
        [Comfort Bicycles],
        [Cruisers Bicycles],
        [Cyclocross Bicycles],
        [Electric Bikes],
        [Mountain Bikes],
        [Road Bikes]
    )
) AS pivot_table;

PIVOT result - one row, seven columns

Seven categories are now seven columns. The row count drops from one-per-category to one overall.

8.3 Adding Row Groups​

Any additional column in the base dataset that is not part of the PIVOT clause automatically becomes a row group. Adding model_year groups the output by year:

SELECT * FROM (
    SELECT category_name, product_id, model_year
    FROM production.products p
    INNER JOIN production.categories c ON c.category_id = p.category_id
) AS t
PIVOT (
    COUNT(product_id)
    FOR category_name IN (
        [Children Bicycles],
        [Comfort Bicycles],
        [Cruisers Bicycles],
        [Cyclocross Bicycles],
        [Electric Bikes],
        [Mountain Bikes],
        [Road Bikes]
    )
) AS pivot_table;

PIVOT with model year row groups

One row per model_year. The category columns contain the product count for that year and category combination.

⚠ Common Mistake - Extra Columns in Base Dataset Every column in the base dataset that is not in FOR or the aggregate becomes a row grouping column. If you include product_name in the base dataset, you get one pivot row per product rather than one per year. Only select the columns PIVOT actually needs.

8.4 Generating the Column List​

Typing every category name manually is error-prone and breaks when new categories are added. Use QUOTENAME() to generate the list automatically:

DECLARE @columns NVARCHAR(MAX) = '';

SELECT @columns += QUOTENAME(category_name) + ','
FROM production.categories
ORDER BY category_name;

SET @columns = LEFT(@columns, LEN(@columns) - 1);

PRINT @columns;
[Children Bicycles],[Comfort Bicycles],[Cruisers Bicycles],[Cyclocross Bicycles],[Electric Bikes],[Mountain Bikes],[Road Bikes]

Copy this output and paste it into the IN list of your PIVOT query. QUOTENAME() wraps each name in square brackets, which handles spaces and special characters in category names.

8.5 Dynamic PIVOT​

A static IN list breaks when new category values are added to the table. Dynamic SQL builds the IN list at runtime and executes it:

DECLARE
    @columns NVARCHAR(MAX) = '',
    @sql     NVARCHAR(MAX) = '';

SELECT @columns += QUOTENAME(category_name) + ','
FROM production.categories
ORDER BY category_name;

SET @columns = LEFT(@columns, LEN(@columns) - 1);

SET @sql = '
SELECT * FROM (
    SELECT category_name, product_id, model_year
    FROM production.products p
    INNER JOIN production.categories c ON c.category_id = p.category_id
) AS t
PIVOT (
    COUNT(product_id)
    FOR category_name IN (' + @columns + ')
) AS pivot_table;';

EXECUTE sp_executesql @sql;

The @columns string is constructed at runtime from the actual data. sp_executesql executes the assembled SQL string. Add a new category to production.categories and this query includes it automatically on the next run.

Interview Angle "What is the limitation of a static PIVOT in SQL Server?" The IN list must be hardcoded at query write time. If the source data adds a new category value, the pivot query must be manually updated. Dynamic SQL using sp_executesql solves this by building the IN list from the data at runtime. The trade-off is that dynamic SQL is harder to read, debug, and secure.

8.6 PIVOT with SUM​

PIVOT works with any aggregate function, not just COUNT. This example pivots total revenue by category:

SELECT * FROM (
    SELECT
        c.category_name,
        oi.order_id,
        oi.quantity * oi.list_price * (1 - oi.discount) AS revenue
    FROM sales.order_items oi
    INNER JOIN production.products p ON p.product_id = oi.product_id
    INNER JOIN production.categories c ON c.category_id = p.category_id
) AS t
PIVOT (
    SUM(revenue)
    FOR category_name IN (
        [Mountain Bikes],
        [Road Bikes],
        [Electric Bikes]
    )
) AS pivot_table;
Mountain Bikes | Road Bikes | Electric Bikes
---------------+------------+---------------
892450.30      | 745210.15  | 421890.00

8.7 UNPIVOT​

UNPIVOT is the reverse operation. It takes columns and converts them back to rows. This is useful when you receive cross-tab data that needs to be loaded into a normalized table.

SELECT store_name, category, revenue
FROM (
    SELECT
        store_name,
        [Mountain Bikes],
        [Road Bikes],
        [Electric Bikes]
    FROM pivot_table
) AS t
UNPIVOT (
    revenue
    FOR category IN (
        [Mountain Bikes],
        [Road Bikes],
        [Electric Bikes]
    )
) AS unpivot_table;
store_name         | category       | revenue
--------------------+----------------+-----------
Santa Cruz Bikes    | Mountain Bikes | 312440.00
Santa Cruz Bikes    | Road Bikes     | 248700.00
Baldwin Bikes       | Mountain Bikes | 289450.30

PIVOT vs UNPIVOT​

PIVOT:    rows -> columns   (normalize to cross-tab)
UNPIVOT:  columns -> rows   (cross-tab to normalize)

Source (normalized):          After PIVOT:
store | category | revenue    store    | Mountain | Road
------+----------+-------     ---------+----------+------
A     | Mountain | 100        A        | 100      | 80
A     | Road     | 80    <=>  B        | 60       | 90
B     | Mountain | 60
B     | Road     | 90         After UNPIVOT: back to normalized

8.8 Putting It Together​

Annual product count report showing how many products each brand released per year, pivoted by year.

SELECT * FROM (
    SELECT
        b.brand_name,
        p.model_year,
        p.product_id
    FROM production.products p
    INNER JOIN production.brands b ON b.brand_id = p.brand_id
) AS t
PIVOT (
    COUNT(product_id)
    FOR model_year IN ([2016], [2017], [2018])
) AS pivot_table
ORDER BY brand_name;
brand_name  | 2016 | 2017 | 2018
-------------+------+------+------
Electra      | 12   | 18   | 14
Haro         | 8    | 11   | 9
Heller       | 3    | 5    | 4
Pure Cycles  | 2    | 3    | 2
Trek         | 22   | 31   | 28

One row per brand, one column per year, product count in each cell.

Summary​

PIVOT converts distinct row values into columns using an aggregate function. It requires three things: the aggregate function, the FOR column whose values become headers, and the IN list of those values. Build a pivot in three steps: select a minimal base dataset, wrap it in a derived table or CTE, then apply the PIVOT operator. Any column in the base dataset not referenced by PIVOT automatically becomes a row group. Use QUOTENAME() to generate the column list from the data. Use dynamic SQL with sp_executesql when the column values can change. UNPIVOT reverses the operation, converting columns back into rows.

Exercises​

8.1 - Write a PIVOT query that shows the number of orders per store per year (2016, 2017, 2018). Store names should be the row labels, years the columns.

8.2 - Modify the query from 11.1 to use SUM of revenue instead of COUNT of orders.

8.3 - Write a query that generates the column list for all distinct model_year values in production.products using QUOTENAME(). Print the result.

8.4 - The following PIVOT query returns too many rows. Identify the problem and fix it:

SELECT * FROM (
    SELECT product_name, category_name, product_id, list_price
    FROM production.products p
    INNER JOIN production.categories c ON c.category_id = p.category_id
) AS t
PIVOT (
    COUNT(product_id)
    FOR category_name IN ([Mountain Bikes], [Road Bikes])
) AS pivot_table;

8.5 - Write a dynamic PIVOT that automatically includes all distinct model_year values from production.products without hardcoding any years.

8.6 - Think About It: PIVOT requires an explicit IN list of values in standard SQL. If a new category is added to production.categories, a static PIVOT query misses it silently without error. What are the trade-offs of using dynamic SQL to solve this, and in what situations would you still prefer a static list?