What is the use of the PIVOT clause in SQL?

The PIVOT clause in SQL is used to transform rows into columns, making it easier to summarize and analyze data in a more readable, report-style format. It’s especially useful when you want to convert categorical data into aggregated columns — for example, converting sales data by month into separate month columns.

To put it simply, PIVOT helps you rotate your data, turning long, detailed rows into a compact, cross-tab view.

For example, let’s say we have a Sales table like this:

If I want to see each product’s monthly sales in columns, I can use a PIVOT query:

SELECT *
FROM (
    SELECT Product, Month, SalesAmount
    FROM Sales
) AS SourceTable
PIVOT (
    SUM(SalesAmount)
    FOR Month IN ([Jan], [Feb])
) AS PivotTable;

This will return:

So, the Month values (Jan, Feb) have become column headers, and their respective sales totals are displayed as column values.

I’ve applied this concept while building financial summary reports and Power BI data models. For example, when preparing a dataset for dashboards, I used PIVOT to create month-wise columns for revenue, making it easier to visualize trends in BI tools.

One challenge with PIVOT is that it requires knowing column names (like months) beforehand. If the categories are dynamic (e.g., new months or products get added), I usually switch to dynamic SQL or handle the pivoting in the reporting layer (like Power BI or Excel) instead.

A limitation is that PIVOT is mostly available in SQL Server and Oracle — other databases like MySQL don’t have a native PIVOT clause, though the same logic can be implemented using CASE WHEN with aggregation.

In summary, PIVOT is great for reshaping and summarizing data — turning long transactional tables into clean, report-ready summaries that are easier to read and analyze.