SQL PIVOT Table – Transform Rows into Columns

In SQL, the PIVOT operation is used to convert row data into columns. It’s extremely useful when you want to generate reports, summaries, or dashboards from normalized data.

What is PIVOT?

PIVOT rotates data from rows into columns, allowing you to aggregate values and display them in a more readable format.

• Think of it like creating a summary table where:
• Rows become columns
• Values are aggregated (SUM, COUNT, etc.)

When to Use PIVOT

• Creating reports (monthly sales, yearly stats)
• Converting category rows into columns
• Dashboard data representation
• Data summarization

Sample Table

Sales Table:

Without PIVOT (Normal Query)

SELECT Employee, Month, Sales
FROM Sales;

Output remains row-based.

Using PIVOT

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

Output

Syntax Breakdown

PIVOT (
    AGGREGATE_FUNCTION(ColumnToAggregate)
    FOR ColumnToPivot IN ([Value1], [Value2], ...)
)

AGGREGATE_FUNCTION → SUM, COUNT, AVG, etc.

• ColumnToAggregate → numeric column (e.g., Sales)
• ColumnToPivot → column whose values become new columns (e.g., Month)
• IN → list of values to convert into columns

Key Points

• PIVOT requires an aggregate function
• You must specify column values manually in IN
• Works mainly in SQL Server (syntax may differ in other DBs)

Dynamic PIVOT (Advanced)

If you don’t know column names in advance (e.g., months), use dynamic SQL:

DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);

SELECT @cols = STRING_AGG(QUOTENAME(Month), ',')
FROM (SELECT DISTINCT Month FROM Sales) AS M;

SET @query = '
SELECT *
FROM (
    SELECT Employee, Month, Sales FROM Sales
) AS Source
PIVOT (
    SUM(Sales) FOR Month IN (' + @cols + ')
) AS P;';

EXEC sp_executesql @query;

PIVOT vs GROUP BY

Conclusion

SQL PIVOT is a powerful tool for transforming and summarizing data into a report-friendly format. While it requires a bit of setup, it can significantly simplify complex reporting queries.