PIVOT is used to rotate rows into columns.

Think of it as converting vertical data into horizontal data for reporting or analytics.

1.Why use PIVOT?

  • Makes aggregated data easier to read
  • Useful for cross-tab reports
  • Reduces the need for multiple CASE statements

2.Basic Syntax

SELECT <non-pivoted columns>,
       [first pivoted column] AS <alias>,
       [second pivoted column] AS <alias>,
       ...
FROM
(
    SELECT <columns to pivot>
    FROM table_name
) AS SourceTable
PIVOT
(
    <aggregate_function>(<value_column>)
    FOR <column_to_pivot> IN ([first pivoted column], [second pivoted column], ...)
) AS PivotTable;

3.Example Table: Sales

Year Region Amount
2024 East 1000
2024 West 1500
2025 East 2000
2025 West 2500

4.Pivot: Year as Row, Region as Columns

SELECT Year, [East], [West]
FROM
(
    SELECT Year, Region, Amount
    FROM Sales
) AS SourceTable
PIVOT
(
    SUM(Amount)
    FOR Region IN ([East], [West])
) AS PivotTable;

Result

Year East West
2024 1000 1500
2025 2000 2500

✅ Rows are now rotated into columns.

5.Pivot with Multiple Aggregates

SQL Server PIVOT only allows one aggregate per pivot, so for multiple aggregates, you need multiple pivot statements or CASE expressions.

6.Dynamic Pivot (Optional)

If column values are unknown, you can generate pivot columns dynamically using dynamic SQL:

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

SELECT @cols = STRING_AGG(QUOTENAME(Region), ',')
FROM (SELECT DISTINCT Region FROM Sales) AS Regions;

SET @query = '
SELECT Year, ' + @cols + '
FROM
(
    SELECT Year, Region, Amount
    FROM Sales
) AS src
PIVOT
(
    SUM(Amount)
    FOR Region IN (' + @cols + ')
) AS pvt;';

EXEC sp_executesql @query;

7.Pivot vs CASE Statement

CASE Example:

SELECT Year,
       SUM(CASE WHEN Region = 'East' THEN Amount ELSE 0 END) AS East,
       SUM(CASE WHEN Region = 'West' THEN Amount ELSE 0 END) AS West
FROM Sales
GROUP BY Year;

Categorized in:

SQL Server,