SQL Pivot and Unpivot — Transform Your SQL Data Views

A teacher wants one class-summary row with counts for different mark bands, rather than separate rows per band.

Pivoting turns selected row categories into report columns. Unpivoting turns selected columns into rows, changing the representation rather than inventing new source facts.

Conditional aggregation uses CASE to choose which values contribute to each aggregate column. SQL Server also has PIVOT and UNPIVOT operators, while their availability and syntax differ elsewhere. UNION ALL can build a column-to-row transformation while retaining duplicates.

Related concepts: Choose Values with CASE and Group and Summarize and Remove Duplicate Results.

Pivot converts row data into column format. It's commonly used to create summary reports where each unique value in a column becomes a new column itself.

SQL · portable concepts; preserved examples identify their engine

CREATE TABLE marks (
  roll_no INT,
  name VARCHAR(50),
  subject VARCHAR(20),
  marks INT
);

INSERT INTO marks VALUES
(1, 'Aarav', 'Maths', 90),
(1, 'Aarav', 'Science', 85),
(1, 'Aarav', 'English', 88),
(2, 'Diya', 'Maths', 80),
(2, 'Diya', 'Science', 92),
(2, 'Diya', 'English', 91);

SQL · portable concepts; preserved examples identify their engine

Not all databases support PIVOT natively, so we use CASE WHEN to simulate it.

SELECT
  roll_no,
  name,
  MAX(CASE WHEN subject = 'Maths' THEN marks END) AS Maths,
  MAX(CASE WHEN subject = 'Science' THEN marks END) AS Science,
  MAX(CASE WHEN subject = 'English' THEN marks END) AS English
FROM marks
GROUP BY roll_no, name;

roll_no | name  | Maths | Science | English
--------+-------+-------+---------+--------
1       | Aarav | 90    | 85      | 88
2       | Diya  | 80    | 92      | 91

Unpivot is used to transform columns into rows — useful when normalizing denormalized reports or preparing data for analysis tools.

SQL · portable concepts; preserved examples identify their engine

-- Unpivot the above pivoted result
SELECT roll_no, name, 'Maths' AS subject, Maths AS marks FROM pivoted_table
UNION ALL
SELECT roll_no, name, 'Science', Science FROM pivoted_table
UNION ALL
SELECT roll_no, name, 'English', English FROM pivoted_table;

If your SQL dialect supports UNPIVOT (like Oracle):

SELECT roll_no, name, subject, marks
FROM pivoted_table
UNPIVOT (
  marks FOR subject IN (Maths, Science, English)
);

• 📄 Displaying subject-wise marks in a single row for each student
• Transforming term-based scores into columnar formats for comparison
• Importing Excel-like formats into normalized tables
• Generating analytics dashboards where subjects are shown as categories

• Always use GROUP BY when pivoting with aggregation
• Use NULL handling for missing data in unpivoted views
• Normalize data before storing; pivot/unpivot only for display/reporting

Hard-coded category columns need a maintenance policy when categories change. NULL handling can differ between an engine’s UNPIVOT operator and a hand-written UNION ALL. Decide the result’s row identity before grouping so multiple assessments are not unintentionally collapsed.

Create one summary row containing the strong and developing student counts.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

SELECT SUM(CASE WHEN marks >= 80 THEN 1 ELSE 0 END) AS strong_students, SUM(CASE WHEN marks < 80 THEN 1 ELSE 0 END) AS developing_students FROM practice_students;

CASE turns each qualifying row into a one for its band. SUM combines those indicators into two report columns.

Pivoting turns selected row categories into report columns. Unpivoting turns selected columns into rows, changing the representation rather than inventing new source facts.

CASE turns each qualifying row into a one for its band. SUM combines those indicators into two report columns.

Continue your understanding with Choose Values with CASE and Group and Summarize and Remove Duplicate Results.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.