SQL Pivot Table: Syntax, Usage, and Examples

A SQL pivot table transforms row-based data into a column-based format, making it easier to analyze, compare, and present information. It’s commonly used in reporting, summarization, and trend analysis. Whether you're aggregating sales, organizing survey responses, or restructuring data for readability, pivot tables help you extract meaningful insights from structured data.

How to Pivot a Table in SQL

Pivoting data in SQL requires using the PIVOT operator, available in SQL Server, or alternative methods such as conditional aggregation for other SQL databases. The core idea is to rotate unique row values into column headers while applying an aggregation function.

Basic Syntax

SELECT column1, column2, [PivotColumn1], [PivotColumn2], ...
FROM (
    SELECT column1, column2, value_column
    FROM some_table
) AS source_table
PIVOT (
    AGGREGATE_FUNCTION(value_column)
    FOR column_to_pivot IN ([PivotColumn1], [PivotColumn2], ...)
) AS pivot_table;

Explanation

• column1, column2 – Fields that remain unchanged in the output.
• value_column – The column whose values get aggregated.
• column_to_pivot – Unique values from this column become new headers.
• AGGREGATE_FUNCTION – An aggregate function (SUM, COUNT, AVG, etc.) applied to value_column.

Example: Pivot Table SQL Query

SELECT product_category, [2023], [2024]
FROM (
    SELECT product_category, sales_year, sales_amount
    FROM Sales
) AS source_table
PIVOT (
    SUM(sales_amount)
    FOR sales_year IN ([2023], [2024])
) AS pivot_table;

In this example, sales_year values become new columns, showing total sales per category for 2023 and 2024.

When to Use Pivot Tables in SQL

Generating Structured Reports

If you need to present data in a summarized, column-oriented format, a pivot table helps create reports that are easier to interpret. A sales report by region and year or a financial breakdown per department are common examples.

Comparing Values Across Categories

Pivoting enables direct comparisons. For example, a retail database might contain sales data by region and year. Instead of listing multiple rows per year, you can pivot the table to display each year as a separate column, making it easier to compare revenue trends.

Transforming Row-Based Data into Readable Output

Sometimes, databases store information in a row-oriented manner, which isn't suitable for reporting or visualization. Converting it into a pivoted format lets you structure the data in a way that better supports analysis and decision-making.

Preparing Data for Business Intelligence

BI tools like Power BI and Tableau often require structured data. A pivot table helps shape the dataset into a format that's easier to work with for dashboards and visualizations.

Examples of Pivot Tables in SQL

Basic Pivot Table SQL Query

This query organizes sales data by region and product category.

SELECT region, [Laptops], [Tablets], [Phones]
FROM (
    SELECT region, product_category, sales_amount
    FROM Sales
) AS source_table
PIVOT (
    SUM(sales_amount)
    FOR product_category IN ([Laptops], [Tablets], [Phones])
) AS pivot_table;

The query converts product_category values into columns and aggregates sales per region.

Pivot Table SQL Server with Multiple Columns

A pivot table can also aggregate multiple values per category, such as total sales and average price.

SELECT region, [Laptops_TotalSales], [Laptops_AvgPrice],
              [Tablets_TotalSales], [Tablets_AvgPrice]
FROM (
    SELECT region, product_category, total_sales, avg_price
    FROM Sales
) AS source_table
PIVOT (
    SUM(total_sales) AS TotalSales, AVG(avg_price) AS AvgPrice
    FOR product_category IN ([Laptops], [Tablets])
) AS pivot_table;

This example pivots two values—total_sales and avg_price—per product category.

Dynamic Pivot Table in SQL

When the number of pivoted columns is unknown beforehand, you can generate a dynamic pivot table. This approach adjusts column headers based on actual data.

DECLARE @columns NVARCHAR(MAX)
DECLARE @query NVARCHAR(MAX)

SELECT @columns = STRING_AGG(QUOTENAME(sales_year), ', ')
FROM (SELECT DISTINCT sales_year FROM Sales) AS years

SET @query = '
SELECT product_category, ' + @columns + '
FROM (
    SELECT product_category, sales_year, sales_amount
    FROM Sales
) AS source_table
PIVOT (
    SUM(sales_amount) FOR sales_year IN (' + @columns + ')
) AS pivot_table;'

EXEC sp_executesql @query;

This query dynamically retrieves years from the Sales table and pivots them.

Learn More About Pivoting in SQL

Converting a Pivot Table Back to Rows

If data is already pivoted but needs to be returned to its original row-based format, you can use UNPIVOT.

SELECT product_category, sales_year, total_sales
FROM pivot_table
UNPIVOT (
    total_sales FOR sales_year IN ([2023], [2024], [2025])
) AS unpivoted_table;

This query reverses the pivoting process, transforming column values back into rows.

Pivot Table vs. Grouped Queries

Pivot tables transform data structure, while GROUP BY only aggregates values without changing column layouts.

SELECT product_category, sales_year, SUM(sales_amount)
FROM Sales
GROUP BY product_category, sales_year
ORDER BY product_category, sales_year;

If you only need total values without transposing columns, GROUP BY is the better choice.

Using Conditional Aggregation as an Alternative

Not all SQL databases support the PIVOT operator. If yours doesn’t, you can achieve the same effect using CASE WHEN inside an aggregate function.

SELECT product_category,
       SUM(CASE WHEN sales_year = 2023 THEN sales_amount ELSE 0 END) AS Sales_2023,
       SUM(CASE WHEN sales_year = 2024 THEN sales_amount ELSE 0 END) AS Sales_2024
FROM Sales
GROUP BY product_category;

This alternative method manually creates pivoted columns based on specific conditions.

Optimizing Pivot Queries for Performance

• Indexing – Ensure that pivoted columns and aggregated values are indexed for faster queries.
• Pre-Aggregation – If performance is critical, consider storing pivoted results in a separate table instead of recalculating on every query.
• Filtering Data – Reduce dataset size using WHERE clauses to limit unnecessary computations.