PIVOT and UNPIVOT

In the previous lesson, you learned about:

which are used for ranking and analytical reporting.

Another powerful SQL feature used extensively in:

is:

These operators allow data to be transformed between:

making reports easier to read and analyze.

What is PIVOT?

PIVOT converts:

It rotates data from row format into column format.

Simple Definition

PIVOT transforms unique row values into separate columns.

Why is PIVOT Needed?

Suppose sales data looks like:

Question:

Desired Output:

This is exactly what PIVOT does.

What is UNPIVOT?

UNPIVOT performs the reverse operation.

It converts:

Simple Definition

UNPIVOT transforms column values into row values.

Example

Input:

Output:

Understanding PIVOT

Normal Data:

Pivoted Data:

Each month becomes a column.

Sample Sales Table

Sample Data

PIVOT Syntax

SQL Server:

Understanding the Syntax

Aggregate Function

Usually:

FOR

Specifies values to convert into columns.

IN

Lists resulting columns.

PIVOT Example

Output

How PIVOT Works

Input:

Output:

Rows become columns.

Multiple Products Example

Input:

Output:

What is UNPIVOT?

UNPIVOT reverses PIVOT.

Transforms:

UNPIVOT Syntax

Sample Pivoted Data

UNPIVOT Query

Output

PIVOT vs GROUP BY

GROUP BY:

PIVOT:

Example:

GROUP BY Output:

PIVOT Output:

More report-friendly.

Dynamic PIVOT

Problem:

Example:

New quarters added.

Instead of:

hardcoding,

Dynamic SQL generates columns automatically.

This advanced topic is common in reporting systems.

Real-World Example: Sales Dashboard

Input:

Output:

using PIVOT.

Real-World Example: Banking

Input:

Output:

Real-World Example: Payroll

Input:

Output:

Real-World Example: University

Input:

Output:

using PIVOT.

Student Marks Example

Input:

Pivot Output:

Employee Attendance Example

Input:

Pivot Output:

Advantages of PIVOT

Better Reporting

Creates dashboard-friendly output.

Easier Analysis

Improves readability.

Reduces Manual Processing

Automatic transformation.

Useful for BI Tools

Power BI, Tableau, SSRS.

Supports Aggregation

Works with SUM, COUNT, AVG.

Advantages of UNPIVOT

Normalizes Data

Converts reports back to row format.

Easier Data Processing

Useful for ETL pipelines.

Better Integration

Works with analytical systems.

Disadvantages

Complex Syntax

Harder for beginners.

Dynamic Columns Challenge

Static pivots require predefined columns.

Performance Cost

Large pivots can be expensive.

Database Differences

Syntax varies across databases.

Common Mistakes

Forgetting Aggregate Function

PIVOT requires aggregation.

Missing Columns in IN()

Output incomplete.

Confusing PIVOT with GROUP BY

They serve different purposes.

Ignoring NULL Values

Pivot results often contain NULLs.

Best Practices

Use Meaningful Column Names

Improve report readability.

Handle NULL Values

Use:

where appropriate.

Use Dynamic PIVOT When Needed

Avoid hardcoded columns.

Test Performance

Large datasets may require optimization.

Prefer PIVOT for Reporting

Especially dashboard development.

Common Interview Questions

What is PIVOT?

A SQL operator that converts rows into columns.

What is UNPIVOT?

A SQL operator that converts columns into rows.

Is PIVOT an aggregation operation?

Yes.

It usually uses aggregate functions like SUM().

What is the opposite of PIVOT?

Where is PIVOT commonly used?

Reporting, analytics, dashboards, and BI systems.

Summary

PIVOT and UNPIVOT are advanced SQL operators used to transform data between row and column formats. They are widely used in reporting systems, dashboards, business intelligence platforms, and analytics applications.

In this lesson, you learned:

• What PIVOT is
• What UNPIVOT is
• PIVOT syntax
• UNPIVOT syntax
• Sales examples
• Student marks examples
• Dashboard reporting
• Advantages and disadvantages
• Best practices

Mastering PIVOT and UNPIVOT is important because data transformation is a fundamental requirement in reporting and analytics systems.

Next Step

Continue to the next lesson:

CASE Expression →