How to Create a Pivot Table in SQL (With Query Examples)

When working with relational databases, we often encounter data stored in a normalized, row-based format. While this structure is excellent for maintaining data integrity and minimizing redundancy, it’s not always ideal for analysis or reporting. That’s where pivot tables come in.

In this guide, we’ll explore what a pivot table is in SQL, its structure, how to create one across different SQL databases like SQL Server, Oracle, MySQL, and PostgreSQL, and some best practices for creating them efficiently.

Let’s start by discussing what a pivot table is in SQL.

What is a pivot table in SQL?

A pivot table in SQL is a technique that rotates data from rows into columns, allowing us to reorganize and summarize data in a more readable format. This transformation is especially useful when we want to aggregate and analyze data across different categories, such as summarizing monthly sales per region or counting employees per department by job title.

By using a pivot SQL operation, we can convert repetitive row values — such as dates, product names, or regions — into columns that make comparisons easier. For instance, instead of listing monthly sales in multiple rows, we can pivot the data to display months as column headers, with total sales figures beneath each.

We use SQL pivot tables to make raw, normalized data more digestible and presentation-ready. Whether we’re generating business intelligence reports or building dashboards, pivoting helps reveal patterns, trends, and outliers quickly. This makes our decision-making process faster and more data-informed.

In the next section, we’ll take a look at the structure of a SQL pivot table.

Structure of a SQL pivot table

Before diving into the database-specific implementations, it’s important to understand the fundamental structure of a pivot table SQL query. A pivot table doesn’t just rearrange data — it strategically reshapes it to answer specific business questions more efficiently.

At its core, a pivot table in SQL follows a simple three-part design:

Part 1: Rows (Row headers)

These represent the categorical data we want to group by. Think of them as the labels that define each record or entity — such as Region, Department, Product, or Customer. These values remain as rows in the final result and are used to break the data down into meaningful segments.

For example, if we want to analyze sales by Region, then Region would be placed in the row.

Part 2: Columns (Pivoted values)

These are the unique values from a chosen column that we want to convert into headers. These new headers are generated from distinct values in one of the fields in our original table — for instance, years, months, product names, or job titles.

If we pivot data based on Year, distinct values like 2022, 2023, and 2024 would become new column names in our pivoted output.

Part 3: Values (Aggregated data)

These are the actual numbers we want to summarize — such as totals, averages, or counts. We typically use aggregate functions like SUM(), AVG(), COUNT(), MAX(), or MIN() to perform the summarization.

For example, if we’re interested in the total Sales, then SUM(Sales) would be used to compute the value under each pivoted column.

Example layout:

Let’s say we have a simple dataset:

A pivot table summarizing total sales per region per year would look like:

Here’s how the components map:

• Rows: Region
• Columns: Distinct values of Year, i.e., 2022, 2023
• Values: Aggregated SUM(Sales)

This structure helps us convert raw, transactional data into an easy-to-read format that supports deeper insight and better decision-making. Understanding this structure is key before we write any pivot table SQL query.

With the structure covered, let’s dive into the database-specific implementations of a SQL pivot table, starting with SQL Server.

How to create a pivot table in SQL Server

SQL Server provides built-in support for pivoting data using the PIVOT operator, which simplifies transforming rows into columns. This is especially useful when we want to summarize or aggregate data across specific categories. Whether it’s grouping sales by region, tracking employee counts by department, or calculating performance metrics, the PIVOT operator helps make complex reporting much easier.

Syntax

Here is the syntax for creating a pivot table using the PIVOT operator in SQL Server:

SELECT <row_headers>, [Column1], [Column2], ..., [ColumnN] 
FROM ( 
    SELECT <row_headers>, <column_to_pivot>, <value_column> 
    FROM <table_name> 
) AS SourceTable 
PIVOT ( 
    <aggregate_function>(<value_column>) 
    FOR <column_to_pivot> IN ([Column1], [Column2], ..., [ColumnN]) 
) AS PivotTable;

In the syntax:

• <row_headers>: Columns that remain in rows (e.g., Region)
• <column_to_pivot>: Column whose unique values become columns (e.g., Year)
• <value_column>: The numeric field we want to aggregate (e.g., Sales)
• <aggregate_function>: Typically SUM, COUNT, AVG, etc.
• [Column1], [Column2], ...: List of values from <column_to_pivot> that will become new columns

Example

Suppose there is a table SalesData:

We’ll use this table in the other examples as well.

We want to pivot the data in this table so that each year becomes a column, showing total sales per region.

Here is the query:

SELECT * 
FROM ( 
    SELECT Region, Year, Sales 
    FROM SalesData 
) AS SourceTable 
PIVOT ( 
    SUM(Sales) 
    FOR Year IN ([2022], [2023]) 
) AS PivotResult;

Copy to clipboard
Copy to clipboard

In this query:

• The inner query selects the three columns we need: Region, Year, and Sales.
• The PIVOT operator aggregates Sales using SUM(Sales).
• The FOR Year IN (...) clause specifies that we want to pivot the values from the Year column, turning 2022 and 2023 into new columns.

The output for this query is a table with one row per Region and one column per Year:

Note: If a region doesn’t have data for a particular year, SQL Server will return NULL by default. We can wrap these values with ISNULL() or COALESCE() to replace NULL with 0 if needed.

Next, let’s learn how to create a pivot table in Oracle.

How to create a pivot table in Oracle

Oracle doesn’t include a built-in PIVOT keyword until version 11g, but since then, it supports a dedicated PIVOT clause similar to SQL Server. This allows us to transform rows into columns efficiently for reporting and data analysis. With Oracle’s syntax, we can perform SQL pivot operations cleanly using standard SQL, making our summaries and comparisons more accessible.

Syntax

Here is the syntax for creating a pivot table using the PIVOT clause in Oracle:

SELECT * 
FROM ( 
    SELECT <row_headers>, <column_to_pivot>, <value_column> 
    FROM <table_name> 
) 
PIVOT ( 
    <aggregate_function>(<value_column>) 
    FOR <column_to_pivot> IN (<column_value_1> AS <alias1>, <column_value_2> AS <alias2>, ...) 
);

In the syntax:

• <row_headers>: Columns that stay as rows (e.g., Region)
• <column_to_pivot>: The column to pivot (e.g., Year)
• <value_column>: The numeric field to aggregate (e.g., Sales)
• <aggregate_function>: Like SUM, COUNT, AVG, etc.
• <column_value>: The specific values to pivot and their aliases (e.g., 2022 AS Y2022)

Example

Considering the same SalesData table, we want to pivot the data in it to show Sales for each Region by Year.

Here is the query:

SELECT * 
FROM ( 
    SELECT Region, Year, Sales 
    FROM SalesData 
) 
PIVOT ( 
    SUM(Sales) 
    FOR Year IN (2022 AS Y2022, 2023 AS Y2023) 
);

Copy to clipboard
Copy to clipboard

In this query:

• The subquery pulls the relevant fields.
• The PIVOT clause aggregates the Sales by Year.
• Each Year becomes a column (Y2022, Y2023), making the data easier to analyze.

Here is the output:

As with SQL Server, NULL appears where no matching value exists in Oracle. We can handle these with NVL() or COALESCE() if desired.

Let’s discuss the process of creating a pivot table in MySQL in the next section.

How to create a pivot table in MySQL

Unlike Oracle or SQL Server, MySQL does not offer a built-in PIVOT operator. However, we can still create pivot table SQL queries using aggregate functions in combination with CASE WHEN statements. While this method needs a bit more manual effort, it offers flexibility and is widely used for generating pivot-like results in MySQL environments.

Syntax

Since MySQL lacks a native PIVOT clause, we use this general syntax:

SELECT <row_headers>, 
    SUM(CASE WHEN <column_to_pivot> = <value1> THEN <value_column> ELSE 0 END) AS <alias1>, 
    SUM(CASE WHEN <column_to_pivot> = <value2> THEN <value_column> ELSE 0 END) AS <alias2>, 
    ... 
FROM <table_name> 
GROUP BY <row_headers>;

In the syntax:

• <row_headers>: The field to group by (e.g., Region)
• <column_to_pivot>: The field with the values you want to turn into columns (e.g., Year)
• <value_column>: The numeric value to summarize (e.g., Sales)
• Each CASE condition checks the pivot column and assigns the value to a specific column alias
• We wrap each CASE in an aggregate function like SUM()

Example

Considering the same SalesData table, we want to display the total Sales by Region for the years 2022 and 2023.

Here is the query:

SELECT Region, 
    SUM(CASE WHEN Year = 2022 THEN Sales ELSE 0 END) AS Y2022, 
    SUM(CASE WHEN Year = 2023 THEN Sales ELSE 0 END) AS Y2023 
FROM SalesData 
GROUP BY Region;

Copy to clipboard
Copy to clipboard

In this query:

• We group the data by Region.
• Each SUM(CASE WHEN...) condition filters and sums Sales for a specific year.

This manually creates a pivot-style layout by year, as shown in the output:

In this example, we use ELSE 0 to avoid NULL values, ensuring that regions with missing data for a given year still appear with a zero.

Let’s understand how to create a pivot table in PostgreSQL next.

How to create a pivot table in PostgreSQL

PostgreSQL doesn’t include a built-in PIVOT clause like SQL Server or Oracle, but it provides powerful tools that let us achieve the same results. We typically use aggregate functions with FILTER clauses to create pivot table SQL queries.

Syntax

Here is the syntax for creating a pivot table in PostgreSQL:

SELECT <row_headers>, 
    <aggregate_function>(<value_column>) FILTER (WHERE <column_to_pivot> = <value1>) AS <alias1>, 
    <aggregate_function>(<value_column>) FILTER (WHERE <column_to_pivot> = <value2>) AS <alias2>, 
    ... 
FROM <table_name> 
GROUP BY <row_headers>;

In the syntax:

• <row_headers>: Columns that remain in rows (e.g., Region)
• <column_to_pivot>: Column whose unique values become columns (e.g., Year)
• <value_column>: The numeric field we want to aggregate (e.g., Sales)
• <aggregate_function>: Typically SUM, COUNT, AVG, etc.
• <table_name>: The table containing the data

Example

Considering the same SalesData table, we want to pivot the data in it to show Sales for each Region by Year.

Here is the query:

SELECT Region, 
    SUM(Sales) FILTER (WHERE Year = 2022) AS Y2022, 
    SUM(Sales) FILTER (WHERE Year = 2023) AS Y2023 
FROM SalesData 
GROUP BY Region;

Copy to clipboard
Copy to clipboard

In this query:

• We use GROUP BY Region to group the data so each region appears only once.
• The SUM(Sales) FILTER (WHERE Year = ...) clause allows us to apply a conditional filter directly inside the aggregation.
• For each row in the output, PostgreSQL calculates:
  • Total sales for 2022 (Y2022)
  • Total sales for 2023 (Y2023)

Here is the output:

Note: Just like SQL Server or Oracle, we may encounter NULL values in PostgreSQL. To replace them with 0, we can use COALESCE().

Best practices for creating SQL pivot tables

Applying these best practices will ensure efficient usage of SQL pivot tables:

• Predefine pivot columns: Whenever possible, know the values that will become columns to simplify the query.
• Use aggregate functions: Always pair your pivot with functions like SUM(), COUNT(), AVG(), etc.
• Handle NULL values: Replace missing values using COALESCE() to avoid blank results.
• Optimize performance: Limit the dataset with WHERE clauses before pivoting.

Conclusion

Creating a pivot table in SQL is a powerful technique for transforming and summarizing complex datasets into formats that are easier to read, analyze, and report on. By rotating data from rows into columns, we gain a clearer view of trends, comparisons, and patterns across categories, making it an essential tool for business intelligence, reporting, and data-driven decision-making.

If you want to expand your knowledge of SQL, check out the Learn SQL course on Codecademy.

Frequently asked questions

1. What is the difference between SQL PIVOT and SQL UNPIVOT?

• PIVOT converts rows into columns, summarizing data.
• UNPIVOT does the opposite — it turns columns into rows, useful for normalizing data formats.

2. Can I combine SQL PIVOT with SQL clauses?

Yes. You can combine PIVOT with other SQL clauses like WHERE, JOIN, and ORDER BY. However, be mindful of the sequence of execution and table aliases.

3. Can I pivot data dynamically in SQL?

In SQL Server and Oracle, dynamic pivoting is possible using dynamic SQL to build the column list at runtime. This is helpful when the column values are not known in advance.

4. What’s the best way to handle NULL values in SQL pivot tables?

Replace NULL values in pivot tables using COALESCE() (works in all databases), ISNULL() (SQL Server), NVL() (Oracle), or IFNULL() (MySQL). For example, COALESCE(pivoted_value, 0) replaces NULL with zero in the results.