PIVOT and UNPIVOT: Making Data Rotation Easy

The Problem PIVOT Solves

Scenario: Sales Data

-- Raw data (normalized):
SalesDate  | Product  | Amount
-----------|----------|--------
2024-01-01 | Widget A | 100
2024-01-01 | Widget B | 200
2024-01-01 | Widget C | 150
2024-01-02 | Widget A | 120
2024-01-02 | Widget B | 180
2024-01-02 | Widget C | 160

-- What you want for reporting:
Date       | Widget A | Widget B | Widget C
-----------|----------|----------|----------
2024-01-01 | 100      | 200      | 150
2024-01-02 | 120      | 180      | 160

-- This is PIVOT: rotate columns to rows (or rows to columns)

The raw data is "normalized" (one value per row). Your report needs it "pivoted" (multiple values per row, with products as columns). PIVOT handles this transformation.

PIVOT Syntax

SELECT *
FROM (
  -- Inner query: SELECT the columns you're pivoting from
  SELECT SalesDate, Product, Amount
  FROM dbo.Sales
) AS SourceData

PIVOT (
  -- Aggregation function (SUM, COUNT, AVG, MAX, etc.)
  SUM(Amount)

  -- FOR clause: which column becomes new columns?
  FOR Product IN ([Widget A], [Widget B], [Widget C])
) AS PivotTable;

Breaking Down PIVOT

FROM (subquery) AS SourceData
  └─ Inner query provides raw data

PIVOT (
  aggregation_function(value_column)
    └─ SUM, COUNT, AVG, MAX, MIN, etc.
    └─ Operates on non-pivoted columns

  FOR pivot_column IN (list_of_values)
    └─ Which column's values become column headers?
    └─ [Widget A], [Widget B], [Widget C] become column names
) AS PivotTable
  └─ Alias for the pivoted result set

Complete PIVOT Example

-- Create and populate table
CREATE TABLE dbo.Sales (
  SalesDate DATE,
  Product NVARCHAR(50),
  Amount DECIMAL(10,2)
);

INSERT INTO dbo.Sales VALUES
('2024-01-01', 'Widget A', 100),
('2024-01-01', 'Widget B', 200),
('2024-01-01', 'Widget C', 150),
('2024-01-02', 'Widget A', 120),
('2024-01-02', 'Widget B', 180),
('2024-01-02', 'Widget C', 160);

-- PIVOT query:
SELECT *
FROM (
  SELECT SalesDate, Product, Amount
  FROM dbo.Sales
) AS SourceData

PIVOT (
  SUM(Amount)
  FOR Product IN ([Widget A], [Widget B], [Widget C])
) AS PivotTable;

-- Result:
-- SalesDate  | Widget A | Widget B | Widget C
-- 2024-01-01 | 100      | 200      | 150
-- 2024-01-02 | 120      | 180      | 160

Key insight: The columns you SELECT in the inner query become:
• Row identifiers (SalesDate stays a row)
• The pivot column (Product becomes columns)
• The value column (Amount is aggregated)
Everything else is implicit grouping.

UNPIVOT: The Reverse Operation

The Problem UNPIVOT Solves

Sometimes you have data in pivoted format (wide) and need to normalize it (long):

-- Wide format (pivoted):
Employee | Jan | Feb | Mar
---------|-----|-----|-----
John     | 100 | 110 | 120
Sarah    | 200 | 190 | 210

-- Normalized format (unpivoted):
Employee | Month | Sales
---------|-------|-------
John     | Jan   | 100
John     | Feb   | 110
John     | Mar   | 120
Sarah    | Jan   | 200
Sarah    | Feb   | 190
Sarah    | Mar   | 210

UNPIVOT Syntax

SELECT *
FROM (
  SELECT Employee, Jan, Feb, Mar
  FROM dbo.SalesByMonth
) AS SourceData

UNPIVOT (
  -- Value column name (output), source columns (inputs)
  Sales
  FOR Month IN (Jan, Feb, Mar)
) AS UnpivotTable;

Complete UNPIVOT Example

CREATE TABLE dbo.SalesByMonth (
  Employee NVARCHAR(50),
  Jan DECIMAL(10,2),
  Feb DECIMAL(10,2),
  Mar DECIMAL(10,2)
);

INSERT INTO dbo.SalesByMonth VALUES
('John', 100, 110, 120),
('Sarah', 200, 190, 210);

-- UNPIVOT query:
SELECT *
FROM (
  SELECT Employee, Jan, Feb, Mar
  FROM dbo.SalesByMonth
) AS SourceData

UNPIVOT (
  Sales
  FOR Month IN (Jan, Feb, Mar)
) AS UnpivotTable;

-- Result:
-- Employee | Month | Sales
-- John     | Jan   | 100
-- John     | Feb   | 110
-- John     | Mar   | 120
-- Sarah    | Jan   | 200
-- Sarah    | Feb   | 190
-- Sarah    | Mar   | 210

PIVOT vs. Alternatives

Method	Readability	Performance	Flexibility	Best For
PIVOT	Good (clean)	Good	Limited (values must be known)	Known column values
CASE Statements	Poor (verbose)	Good	Excellent	Complex logic, dynamic columns
CROSS APPLY	Medium	Good	Excellent	Multiple aggregations
Dynamic SQL	Poor	Good	Excellent	Unknown column values

Common Issues & Solutions

Issue 1: "Unknown Column Values"

-- PIVOT requires hard-coded column values:
FOR Product IN ([Widget A], [Widget B], [Widget C])

-- If products are dynamic, use dynamic SQL:
DECLARE @columns NVARCHAR(MAX);
SELECT @columns = STRING_AGG('[' + Product + ']', ',')
FROM (SELECT DISTINCT Product FROM dbo.Sales) AS t;

DECLARE @sql NVARCHAR(MAX) = '
  SELECT *
  FROM (SELECT SalesDate, Product, Amount FROM dbo.Sales) AS SourceData
  PIVOT (SUM(Amount) FOR Product IN (' + @columns + ')) AS PivotTable
';

EXEC sp_executesql @sql;

Issue 2: "Multiple Values Per Group"

Error: "Ambiguous column name" or unexpected aggregation.
Cause: More than one value per pivot group.
Solution: Add ROW_NUMBER() or ensure aggregation is unambiguous.

-- If SalesDate, Product combination has multiple amounts,
-- you need aggregation or ranking:

SELECT *
FROM (
  SELECT SalesDate, Product, Amount,
         ROW_NUMBER() OVER (PARTITION BY SalesDate, Product ORDER BY Amount DESC) AS rn
  FROM dbo.Sales
  WHERE rn = 1  -- Take first row per group
) AS SourceData

PIVOT (
  SUM(Amount)
  FOR Product IN ([Widget A], [Widget B], [Widget C])
) AS PivotTable;

Issue 3: "NULL in Output"

-- PIVOT produces NULLs for missing combinations
-- Replace with COALESCE or ISNULL:

SELECT
  SalesDate,
  ISNULL([Widget A], 0) AS [Widget A],
  ISNULL([Widget B], 0) AS [Widget B],
  ISNULL([Widget C], 0) AS [Widget C]
FROM (
  SELECT SalesDate, Product, Amount
  FROM dbo.Sales
) AS SourceData

PIVOT (
  SUM(Amount)
  FOR Product IN ([Widget A], [Widget B], [Widget C])
) AS PivotTable;

Performance Considerations

PIVOT indexes the pivot column: Having an index on the pivot column (Product) helps performance.
Inner query matters: Filter aggressively in the inner query (WHERE, JOIN). PIVOT operates on the result set, not raw data.
Aggregation is expensive: SUM over millions of rows takes time. Pre-aggregate if possible.
Dynamic SQL overhead: Dynamic SQL adds parsing overhead. Cache the plan if possible (sp_executesql with parameter declarations).

When to Use Each

Use PIVOT When:

Column values are known and fixed
You want clean, readable SQL
Aggregating summarized data
Building reports (fixed structure)

Use CASE When:

Column values are unknown or dynamic
Need complex conditional logic per column
Pivoting isn't the main operation (it's part of a larger query)

Use CROSS APPLY When:

Multiple aggregations per row
Need flexibility with window functions
Combining PIVOT-like logic with other operations

The Bottom Line

PIVOT and UNPIVOT are not as scary as they look. Yes, they have unusual syntax. But they solve a real problem—transforming data between normalized and denormalized formats—and they do it cleanly.

Start with PIVOT when you know your columns. If you need flexibility, switch to CASE statements or dynamic SQL. Don't avoid PIVOT out of fear; embrace it when it fits, and you'll write cleaner reports and dashboards.