SQL Pivot and Unpivot - Transform Your SQL Data Views

Introduction

In real-world reporting — especially in schools — we often want to flip how data is viewed. Think of a marksheet: instead of having one row per subject, we want one row per student and each subject as a column. This transformation is called a Pivot. The opposite — turning columns back into rows — is Unpivot.

What is Pivot?

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.

Sample Table: marks

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);

Pivoting Using CASE + Aggregation

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

What is Unpivot?

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

Unpivot Using UNION ALL

-- 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)
);

Real-World School Use Cases

• 📄 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

Best Practices

• 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

Summary

Pivot and Unpivot empower you to reshape your data based on reporting needs. Whether you’re designing a student marksheet or feeding analytics tools, these transformations give your SQL queries real versatility. In a school setting, this could mean turning detailed records into principal-ready dashboards.

What’s Next?

Up next: SQL Interview Questions — a curated list of practical, real-world SQL problems and how to solve them with clarity.