The SQL PIVOT operation transforms table data from rows into columns, improving readability and making analysis more efficient. It is especially useful for reporting and summarizing data, therefore, this feature becomes an indispensable tool for data analysts. This article explains how the PIVOT function works, the syntax of this function, key features, and practical use cases.
The SQL PIVOT operator makes data easier to read and compare by transforming rows into columns. It restructures a table so that each unique value in a chosen column becomes a new column in the result set. PIVOT in SQL is typically used with aggregate functions to summarize large datasets and present them in a more organized format. This makes it especially useful for data analysis and reporting.
SQL PIVOT is supported natively in SQL Server and Oracle. However, MySQL and PostgreSQL do not include built-in pivot functionality. In those systems, similar results can be achieved using alternative techniques.
This article will focus on how to use the native pivot feature in SQL Server.
The SQL PIVOT operator offers several advantages when transforming row-based data into a column-based format:
Queries that use SQL PIVOT are typically more concise, easier to read, and may benefit from internal optimizations by the SQL engine. However, if the pivot columns are dynamic or need to be determined at runtime, dynamic SQL is required, and manual aggregation may provide greater flexibility.
Use SQL PIVOT when:
Use manual aggregation when:
We've outlined the benefits of the SQL PIVOT operator to explain why it's a popular feature among SQL Server users. Now, let's take a closer look at how SQL PIVOT works in practice.
A SQL Server PIVOT query relies on three core elements:
Next, we'll examine the syntax in more detail.
Below is the basic syntax of a PIVOT query in SQL Server:
SELECT
non_pivoted_column, pivot_column1, pivot_column2, ...
FROM
(
SELECT column_list
FROM source_table
) AS source_table
PIVOT
(
aggregate_function(pivot_column)
FOR pivot_column IN (pivot_column1, pivot_column2, ...)
) AS pivot_table;
This query consists of three core components: the SELECT statement, the subquery, and the PIVOT operator. Let’s break each down:
SELECT statement
Subquery
The subquery prepares the dataset for pivoting:
PIVOT clause
With the SQL PIVOT syntax explained, we can proceed to try the functionality in real-world scenarios.
For our demonstration of how to use PIVOT in SQL, we use the popular AdventureWorks2022 test database. We'll illustrate our queries with dbForge Studio for SQL Server, a robust, multi-featured SQL Server IDE that covers all database-related tasks both on-premise and in the cloud.
Assume we need to obtain the sales data for each product, broken down by sales territory. We use the PIVOT operator for that. The SQL Server PIVOT example is below.
SELECT
ProductName
,[Northwest]
,[Northeast]
,[Southwest]
,[Southeast]
FROM (SELECT
p.Name AS ProductName
,st.Name AS Territory
,soh.TotalDue
FROM Sales.SalesOrderHeader soh
INNER JOIN Sales.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID
INNER JOIN Production.Product p
ON sod.ProductID = p.ProductID
INNER JOIN Sales.SalesTerritory st
ON soh.TerritoryID = st.TerritoryID) AS SourceTable
PIVOT (
SUM(TotalDue)
FOR Territory IN ([Northwest], [Northeast], [Southwest], [Southeast])
) AS PivotTable
ORDER BY ProductName;
In this example, we begin by preparing the base dataset using a subquery. This subquery retrieves data from the SalesOrderHeader, SalesOrderDetail, Product, and SalesTerritory tables. Each row in the resulting dataset represents a product sold in a specific region, along with the total due amount for the corresponding order.
To assemble this dataset, we apply multiple JOIN clauses to combine order details, product information, and regional data.
Next, the PIVOT clause transforms region values into columns. The FOR keyword specifies that values from the Territory column should be used as column headers. The SUM aggregate function combines matching rows to calculate the total sales amount for each product and region. The IN clause defines the region names that will appear as columns in the output.
Finally, the ORDER BY clause sorts the result set to improve readability.
The above example is the standard PIVOT query with the values known in advance. However, our tutorial needs to explore the dynamic pivoting cases.
In some cases, the values to be pivoted are not known in advance, making it impossible to specify them in the IN (...) clause. To handle such scenarios, you can use a dynamic SQL PIVOT. This approach constructs a query string with the required column values and executes it using either EXEC or the sp_executesql stored procedure.
Why and when to use dynamic pivoting:
Assume we need to view the total sales amount per product category, broken down by month. In this scenario, instead of hardcoding the months, we'll use dynamic SQL to pivot by available months in the data.
Execute the below query:
DECLARE @columns NVARCHAR(MAX)
,@sql NVARCHAR(MAX);
-- Step 1: Get the list of distinct month names dynamically from OrderDate
SELECT
@columns = STRING_AGG(QUOTENAME(OrderMonth), ',')
FROM (SELECT DISTINCT
FORMAT(OrderDate, 'MMM') AS OrderMonth
FROM Sales.SalesOrderHeader) AS MonthList;
-- Step 2: Build the dynamic SQL query
SET @sql = '
SELECT CategoryName, ' + @columns + '
FROM (
SELECT
PC.Name AS CategoryName,
FORMAT(SOH.OrderDate, ''MMM'') AS OrderMonth,
SOD.LineTotal
FROM Sales.SalesOrderHeader AS SOH
INNER JOIN Sales.SalesOrderDetail AS SOD
ON SOH.SalesOrderID = SOD.SalesOrderID
INNER JOIN Production.Product AS P
ON SOD.ProductID = P.ProductID
INNER JOIN Production.ProductSubcategory AS PSC
ON P.ProductSubcategoryID = PSC.ProductSubcategoryID
INNER JOIN Production.ProductCategory AS PC
ON PSC.ProductCategoryID = PC.ProductCategoryID
) AS SourceTable
PIVOT (
SUM(LineTotal)
FOR OrderMonth IN (' + @columns + ')
) AS PivotTable
ORDER BY CategoryName;
';
-- Step 3: Execute the dynamic SQL
EXEC sp_executesql @sql;
First, the query dynamically generates a list of month abbreviations to use as column headers in the pivot table. The expression FORMAT(OrderDate, 'MMM') returns the three-letter month abbreviation (such as Jan, Feb), and DISTINCT ensures each month appears only once. The QUOTENAME() function wraps each month in square brackets (like [Jan]), as required by dynamic SQL and the PIVOT operator. These values are then combined into a comma-separated string using STRING_AGG(...) and stored in the @columns variable.
Next, a dynamic SQL query is constructed to pivot the monthly sales totals by product category. A subquery with multiple JOINs retrieves the necessary data from the database, producing a dataset with category names, month names, and sales totals. The PIVOT operator then applies the SUM aggregate function and transforms the month values into columns. The list of pivot columns is injected dynamically via ' + @columns + ', so there is no need to hardcode the month names.
This approach is useful when the set of pivot columns changes over time, as it automatically adapts to newly added months. It allows you to generate a monthly breakdown of product sales by category without manually updating the query.
The PIVOT feature in SQL Server offers significant advantages, but it also comes with limitations and common challenges for data professionals. Below are the most frequent issues encountered when using SQL PIVOT, along with practical solutions.
Pivot operations can be resource-intensive, especially when working with large datasets. Since pivoting involves grouping, aggregation, and data transformation, it requires considerable CPU and memory resources. Additionally, PIVOT is often used within subqueries, which may cause SQL Server to ignore beneficial indexes on pivoted columns. JOINs included in the query can further degrade performance.
Solution: Pre-aggregate or filter the dataset using a common table expression (CTE) or temporary table before applying PIVOT. Ensure critical columns are properly indexed.
PIVOT results often include NULL values when there are no matching rows for a specific pivoted column. Handling NULL in SQL PIVOT queries can lead to incorrect assumptions in calculations, as NULL is not the same as 0. Aggregation functions may also return blanks instead of zeros, which can skew summaries and reports.
Solution: Use ISNULL() or COALESCE() in the outer query to replace NULL values with default values like 0.
SQL PIVOT requires a hardcoded list of output column names in the IN (...) clause. If these names conflict with SQL-reserved keywords or duplicate existing column names, runtime errors may occur. Special characters in column names can also cause issues.
Solution: Use the QUOTENAME() function when building dynamic SQL to safely include column names with reserved words or special characters.
Standard PIVOT queries are static; they don't automatically adapt when new values appear in the pivot column. Supporting dynamic pivoting is possible but requires more complex logic, typically involving dynamic SQL.
Solution: Use stored procedures or reporting tools to encapsulate dynamic pivot logic, improving maintainability and flexibility.
These common SQL PIVOT issues can cause difficulties when working with this operator, but they can be successfully overcome.
Earlier in the article, we mentioned that similar results to those produced by the PIVOT operator can be achieved using alternative methods, especially when discussing PIVOT support across different RDBMSs.
One of the most popular SQL PIVOT alternatives is conditional aggregation using CASE statements. Below is a comparison of SQL pivot methods, highlighting the differences between the SQL PIVOT and the CASE statement approaches.
| Feature | SQL PIVOT | CASE Statement Approach |
| Purpose | Transforms rows into columns | Achieves similar effect manually using GROUP BY + CASE |
| Syntax | Compact for known, fixed column values | Verbose but flexible |
| Flexibility | Less flexible: requires known values in the IN clause | Highly flexible: allows complex logic |
| Readability | Cleaner for simple, static pivots | More readable for complex logic with conditions |
| Dynamic columns | Limited: requires dynamic SQL for changing pivot values | Easier to build dynamically (especially with string concatenation) |
| Custom logic | Limited - mainly numeric aggregations | Fully flexible inside each CASE expression |
| Performance | Usually similar; depends on indexes and engine optimization | Often equally performant; sometimes more optimizable |
| Error handling | Requires careful quoting of column values | Fewer risks with column naming and formatting |
| Portability | Not supported by all RDBMSs | Works in almost all SQL dialects |
| Learning curve | Requires understanding specific syntax | Easier to grasp with basic SQL knowledge |
| Use case | Ideal for straightforward pivoting | Better for customized aggregations |
When to use PIVOT:
When to use CASE + aggregation:
We've covered the most common use cases for PIVOT, but SQL Server also provides advanced methods that enable more precise and comprehensive results. In this section, we’ll explore these advanced SQL PIVOT approaches.
Using multiple aggregate functions with PIVOT in SQL Server requires additional steps, as SQL Server does not support multiple aggregations in a single PIVOT operation. To work around this limitation, you can use multiple PIVOTs and join them.
-- Pivot Quantity
WITH PivotQty
AS
(SELECT
ProductID
,[7] AS JulQty
,[8] AS AugQty
FROM (SELECT
ProductID
,MONTH(ShipDate) AS ShipMonth
,OrderQty
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE YEAR(ShipDate) = 2011
AND MONTH(ShipDate) IN (7, 8)) AS Source
PIVOT (
SUM(OrderQty)
FOR ShipMonth IN ([7], [8])
) AS P),
-- Pivot TotalDue
PivotTotal
AS
(SELECT
ProductID
,[7] AS JulAmount
,[8] AS AugAmount
FROM (SELECT
ProductID
,MONTH(ShipDate) AS ShipMonth
,LineTotal
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE YEAR(ShipDate) = 2011
AND MONTH(ShipDate) IN (7, 8)) AS Source
PIVOT (
SUM(LineTotal)
FOR ShipMonth IN ([7], [8])
) AS P)
SELECT
q.ProductID
,q.JulQty
,t.JulAmount
,q.AugQty
,t.AugAmount
FROM PivotQty q
JOIN PivotTotal t
ON q.ProductID = t.ProductID;
Using SQL PIVOT with CTEs enables us to prepare and filter the dataset within a CTE at the first stage, and then apply the PIVOT operation to that result, transforming rows into columns. This approach makes the complex PIVOT queries clearer, as it separates data preparation from data transformation, resulting in clearer and more concise queries.
WITH ShippedProducts
AS
(SELECT
p.Name AS ProductName
,MONTH(soh.ShipDate) AS ShipMonth
,sod.OrderQty
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID
JOIN Production.Product p
ON sod.ProductID = p.ProductID
WHERE YEAR(soh.ShipDate) = 2011
AND MONTH(soh.ShipDate) IN (7, 8, 9))
SELECT
ProductName
,[7] AS July
,[8] AS August
,[9] AS September
FROM ShippedProducts
PIVOT (
SUM(OrderQty)
FOR ShipMonth IN ([7], [8], [9])
) AS PivotTable;
UNPIVOT is a SQL operator that transforms columns into rows and can reverse a PIVOT operation, flattening columns into rows. This option is useful for transforming, analyzing, and then normalizing data back for storage or export.
One of the previous queries transformed rows into columns to view the sales data by region. Assume that we have retrieved the necessary information and want to return the data to its initial state for further processing. Here is the UNPIVOT query that reverses the result of your pivoted query.
SELECT
ProductName
,Territory
,TotalDue
FROM (SELECT
ProductName
,[Northwest]
,[Northeast]
,[Southwest]
,[Southeast]
FROM (SELECT
p.Name AS ProductName
,st.Name AS Territory
,soh.TotalDue
FROM Sales.SalesOrderHeader soh
INNER JOIN Sales.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID
INNER JOIN Production.Product p
ON sod.ProductID = p.ProductID
INNER JOIN Sales.SalesTerritory st
ON soh.TerritoryID = st.TerritoryID) AS SourceTable
PIVOT (
SUM(TotalDue)
FOR Territory IN ([Northwest], [Northeast], [Southwest], [Southeast])
) AS PivotTable) AS PivotedResult
UNPIVOT (
TotalDue FOR Territory IN ([Northwest], [Northeast], [Southwest], [Southeast])
) AS UnpivotedResult
ORDER BY ProductName, Territory;
In general, the usage of this or that advanced technique depends on the particular work scenario and your goals:
Transforming and summarizing data are essential for effective analysis and reporting. SQL Server’s PIVOT function enhances these processes by producing clearer, more concise, and visually meaningful results. Mastering SQL PIVOT is, therefore, a valuable skill for any SQL user. In this article, we explained the SQL PIVOT feature and demonstrated its use through various scenarios, complete with examples.
To illustrate how SQL PIVOT queries are executed, we used dbForge Studio for SQL Server. This IDE simplifies query creation with features like code auto-completion, syntax validation, code analysis, and formatting.
Additionally, dbForge Studio includes tools that help visualize the necessary pivot table in SQL Server and analyze data interactively without writing complex code. A fully functional 30-day free trial of dbForge Studio is available, allowing you to explore all its features under your own workload.
Yes, but the method depends on the database system. In SQL Server, you can use dynamic SQL to generate column names at runtime for the PIVOT clause. In Oracle, dynamic SQL can also be used with PIVOT via the EXECUTE IMMEDIATE statement.
PostgreSQL and MySQL don't support PIVOT natively, so dynamic pivoting must be implemented manually using CASE expressions or procedural logic.
These are informal terms. Pivot up (PIVOT) refers to transforming row values into column headers to summarize or aggregate data. Pivot down (UNPIVOT) refers to the reverse: converting columns back into rows.
PIVOT transforms rows into columns, typically for aggregation. UNPIVOT transforms columns into rows, often to normalize data.
Use PIVOT when you need a cross-tab view of your data, and UNPIVOT when you need to flatten a wide table into a vertical format.
PIVOT is natively supported in SQL Server and Oracle. It is not directly available in MySQL, PostgreSQL, or SQLite, but similar results can be achieved using CASE statements or custom logic.