Pivot and unpivot concepts in SQL

Introduction

Pivot and unpivot help you change how data is shown. Pivot turns rows into columns, and unpivot turns columns into rows. This makes data easier to read or analyze.

You want to see sales numbers for each product as columns instead of rows.

You have survey data with answers in columns and want to list them as rows.

You want to compare monthly expenses side by side in columns.

You need to prepare data for a report that requires a different layout.

You want to summarize data by turning detailed rows into a summary table.

Syntax

SQL

/* Pivot syntax (example in SQL Server) */
SELECT <columns>, [PivotColumn1], [PivotColumn2], ...
FROM
(
  SELECT <columns>, <pivot_column>, <value_column>
  FROM <table>
) AS SourceTable
PIVOT
(
  SUM(<value_column>)
  FOR <pivot_column> IN ([PivotColumn1], [PivotColumn2], ...)
) AS PivotTable;

/* Unpivot syntax (example in SQL Server) */
SELECT <columns>, UnpivotColumn, UnpivotValue
FROM
(
  SELECT <columns>, [Column1], [Column2], ...
  FROM <table>
) AS SourceTable
UNPIVOT
(
  UnpivotValue FOR UnpivotColumn IN ([Column1], [Column2], ...)
) AS UnpivotTable;

Pivot turns row values into columns to summarize data.

Unpivot turns columns into rows to normalize or list data.

Examples

This query turns product names from rows into columns showing sales per year.

SQL

/* Pivot example: Show total sales per product per year */
SELECT Year, [Apples], [Oranges], [Bananas]
FROM
(
  SELECT Year, Product, Sales
  FROM SalesData
) AS Source
PIVOT
(
  SUM(Sales)
  FOR Product IN ([Apples], [Oranges], [Bananas])
) AS PivotTable;

This query turns month columns into rows with month names and sales values.

SQL

/* Unpivot example: Convert monthly sales columns into rows */
SELECT Year, Month, Sales
FROM
(
  SELECT Year, Jan, Feb, Mar
  FROM MonthlySales
) AS Source
UNPIVOT
(
  Sales FOR Month IN ([Jan], [Feb], [Mar])
) AS UnpivotTable;

Sample Program

This creates a table with sales data, then pivots product sales into columns by year.

SQL

CREATE TABLE SalesData (Year INT, Product VARCHAR(20), Sales INT);
INSERT INTO SalesData VALUES
(2023, 'Apples', 100),
(2023, 'Oranges', 150),
(2023, 'Bananas', 120),
(2024, 'Apples', 130),
(2024, 'Oranges', 160),
(2024, 'Bananas', 140);

SELECT Year, [Apples], [Oranges], [Bananas]
FROM
(
  SELECT Year, Product, Sales
  FROM SalesData
) AS Source
PIVOT
(
  SUM(Sales)
  FOR Product IN ([Apples], [Oranges], [Bananas])
) AS PivotTable;

Important Notes

Pivot and unpivot are useful for changing data layout without changing the data itself.

Not all SQL databases support PIVOT and UNPIVOT keywords; sometimes you use CASE statements instead.

Always check your database documentation for exact syntax and support.

Summary

Pivot changes rows into columns to summarize data.

Unpivot changes columns into rows to list data.

These help make data easier to understand and use in reports.