What Is SQL PIVOT?

SQL PIVOT Explained

SQL PIVOT converts rows into columns, letting you build cross-tab reports with concise syntax. This guide answers key questions and shows best practices.

What Is SQL PIVOT?

SQL PIVOT is a relational operator that rotates a result set by turning the unique values of one column into multiple columns and aggregating another column’s values. It simplifies cross-tab and summary reports without writing multiple CASE expressions.

When Should I Use SQL PIVOT?

Use PIVOT when you must display measures across distinct categories, such as monthly totals per product or survey answers per respondent. It shines in reporting layers where people expect columns, not rows. Avoid it for OLTP workloads that favor row-oriented structures.

How Does the SQL PIVOT Syntax Work?

A PIVOT query wraps a sub-query that returns the base rows. You specify the aggregation function, the column holding values to aggregate, the column whose distinct values become new columns, and an optional list of target column names.

How Do I Write a Basic SQL PIVOT Query?

Start with a SELECT that fetches the grouping column, pivot column, and value column. Feed it to the PIVOT clause, define the aggregate, and list the columns. Finally, SELECT from the pivoted table to view the rotated data.

Example: Pivoting Monthly Sales

The query below shows total sales per product across Q1 months.

SELECT *
FROM (
SELECT product, month, revenue
FROM sales
WHERE month IN ('Jan','Feb','Mar')
) src
PIVOT (
SUM(revenue) FOR month IN ('Jan' AS Jan, 'Feb' AS Feb, 'Mar' AS Mar)
) p;

How Do I Create Dynamic PIVOT Columns?

Static IN lists break when new categories appear. Build the column list at runtime with string aggregation functions, then execute the generated SQL using prepared statements. Dynamic pivots let your report adjust automatically to new data.

What Are the Limitations of SQL PIVOT?

PIVOT demands known column names at parse time unless you generate SQL dynamically. It returns NULL for missing combinations, so you may need COALESCE. Performance can degrade if the sub-query is large because PIVOT still scans all rows before aggregation.

What Are Best Practices for SQL PIVOT?

Filter early to reduce row count, aggregate only required columns, and name pivoted columns clearly. Use COALESCE to replace NULL with zeros for numeric reports. Keep dynamic pivot code in views or stored procedures to centralize logic.

How Does PIVOT Compare to CASE Statements?

Both techniques rotate data, but PIVOT is declarative and concise, while CASE statements give more control and portability. Choose PIVOT for quick summaries in databases that support it, and CASE when you need cross-platform SQL or complex calculations.

Key Takeaways on SQL PIVOT

SQL PIVOT converts rows into columns with minimal code—ideal for reporting. Know its syntax, limitations, and best practices to produce fast, readable cross-tabs. Combine it with dynamic SQL for adaptable dashboards.