Back to ADVANCED ANALYTICS - PIVOT/UNPIVOT, INDEXING, ORDER OF EXECUTION

PIVOT and UNPIVOT in SQL

8 minutesVideo LessonPDF notes

🎯 Free Guest Mode: You are learning for free. Sign in to save your completion progress and quiz answers.

Loading progress...

Key moments

Relational Operators Overview — The instructor defines PIVOT and UNPIVOT as relational operators for converting rows to columns and columns to rows.
Sample Dataset Setup — The instructor presents the employee table schema containing employee_id, department, and salary columns.
Writing PIVOT Queries — The instructor constructs a PIVOT query using SUM(salary) to generate sales and accounting columns.
Analyzing PIVOT Output — The instructor examines how pivoted columns display aggregated salaries and populate missing intersections with NULL.
Writing UNPIVOT Queries — The instructor writes an UNPIVOT query on the derived pivoted table to restore the original row-based department structure.

PDF notes

Bottom line: You will transform relational table layouts in SQL by rotating categorical row values into distinct columns using the PIVOT operator and reversing wide column structures back into normalized rows using UNPIVOT. You will apply aggregations to cross-tabulate reporting metrics and manage missing category intersections across transformed datasets.

This lesson on PIVOT and UNPIVOT in SQL is hands-on and example-driven. You will transform relational table layouts in SQL by rotating categorical row values into distinct columns using the PIVOT operator and reversing wide column structures back into normalized rows using UNPIVOT. You will apply aggregations to cross-tabulate reporting metrics and manage missing category intersections across transformed datasets.

What You'll Be Able To Do

Transform row-level category values into distinct column headers using the PIVOT operator.
Aggregate metric values using functions like SUM within cross-tabulation clauses.
Reconstruct normalized row records from column-oriented datasets using the UNPIVOT operator.
Structure derived table aliases and select column lists required by PIVOT and UNPIVOT syntax.
Identify and interpret NULL values generated at empty category intersections during table transformations.

Detailed Concept Walkthrough

1. Relational Transposition With PIVOT

The PIVOT operator rotates row-based category values into distinct column headers to produce summarized, cross-tabulated reporting views. It reshapes a tall, narrow table into a wider table format.

Mechanism: The PIVOT operator inspects values inside a specified column (such as department) and rotates distinct categories (such as sales and accounting) into new column attributes on the resulting dataset. This transforms narrow, vertical datasets into wider, horizontal presentation formats suitable for analytical inspection.
Syntax Rule: The outer SELECT list must explicitly request the non-pivoted identifiers alongside the newly declared pivoted column names. An alias for the pivoted subquery result (e.g., AS pivot_table) is mandatory in SQL syntax to treat the pivoted set as a valid derived relational source.
Data Representation: When an individual entity does not possess records for a specific pivoted category, SQL populates the corresponding new column intersection with a NULL value. For instance, an employee belonging solely to sales will automatically receive a NULL under the accounting column.

-- Transform department row values into dedicated columns
SELECT 
    employee_id,
    sales,
    accounting
FROM employee
PIVOT (
    SUM(salary)
    FOR department IN (sales, accounting)
) AS pivot_table;

Key Takeaway: PIVOT reshapes categorical rows into separate columns and populates absent category intersections with NULL.

2. Aggregation Mechanics In PIVOT

Pivoting requires aggregating metric values because multiple source rows can map to a single entity-column intersection. The specified aggregate function consolidates these values during transposition.

Aggregation Requirement: The PIVOT clause mandates an aggregate function, such as SUM(salary), to condense intersecting metric values into the target column cells. Without an aggregate function, SQL cannot resolve multiple row records corresponding to the same grouping key.
Grouping Context: Any column present in the source table that is not specified inside the aggregate function or the FOR ... IN clause automatically forms the implicit grouping key. In the employee table example, employee_id becomes the row-level anchor around which values are pivoted.
Under the Hood: The SQL execution engine computes grouping aggregations across each distinct entity and redistributes the summarized numbers into the explicit target columns declared in the IN list. Missing intersections bypass aggregation calculations and immediately yield NULL.

-- Aggregating salaries during column transposition
SELECT 
    employee_id,
    sales,
    accounting
FROM employee
PIVOT (
    -- Mandatory aggregate function to summarize pivoted measures
    SUM(salary)
    FOR department IN (sales, accounting)
) AS pivot_table;

Key Takeaway: Every PIVOT operation requires an explicit aggregate function to consolidate values into new columns.

3. Normalizing Columns With UNPIVOT

The UNPIVOT operator performs the inverse of PIVOT by converting existing column headers back into normalized row values. It unpacks wide tables into tall transactional records.

Mechanism: The UNPIVOT operator extracts column header names (e.g., sales, accounting) and writes them as values inside a single target descriptor column (e.g., department), while placing the intersecting cell contents into a value column (e.g., salary).
Structural Difference: Unlike PIVOT, UNPIVOT does not utilize an aggregate function (such as SUM) because it is splitting existing column values into individual row items rather than condensing multiple rows. You specify the target value column directly before the FOR ... IN clause.
Handling NULLs: During the unpivoting process, column intersections that contain NULL are excluded from the output rows, naturally restoring the original normalized transactional structure of the dataset.

-- Reverse cross-tabulated columns back into transactional rows
SELECT 
    employee_id,
    department,
    salary
FROM (
    SELECT employee_id, sales, accounting
    FROM employee
    PIVOT (SUM(salary) FOR department IN (sales, accounting)) AS p_src
) AS P
UNPIVOT (
    salary
    FOR department IN (sales, accounting)
) AS unpivot_table;

Key Takeaway: UNPIVOT normalizes cross-tabulated columns back into row records without requiring aggregate functions.

Topics Covered in PIVOT and UNPIVOT in SQL

Relational Operators Overview (0:00 - 0:30) — The instructor defines PIVOT and UNPIVOT as relational operators for converting rows to columns and columns to rows.
Sample Dataset Setup (0:30 - 1:00) — The instructor presents the employee table schema containing employee_id, department, and salary columns.
Writing PIVOT Queries (1:00 - 2:15) — The instructor constructs a PIVOT query using SUM(salary) to generate sales and accounting columns.
Analyzing PIVOT Output (2:15 - 3:00) — The instructor examines how pivoted columns display aggregated salaries and populate missing intersections with NULL.
Writing UNPIVOT Queries (3:00 - 4:00) — The instructor writes an UNPIVOT query on the derived pivoted table to restore the original row-based department structure.

SQL Cheat Sheet

PIVOT (SUM(val_col) FOR cat_col IN (val1, val2)) AS alias — Rotates row category values into columns with aggregation

SELECT employee_id, sales, accounting FROM employee PIVOT (SUM(salary) FOR department IN (sales, accounting)) AS p;

UNPIVOT (val_col FOR cat_col IN (col1, col2)) AS alias — Rotates column headers back into normalized row values

SELECT employee_id, department, salary FROM P UNPIVOT (salary FOR department IN (sales, accounting)) AS u;

SUM(salary) — Aggregates metric values mapped into pivoted columns

PIVOT (SUM(salary) FOR department IN (sales, accounting)) AS p

FOR department IN (sales, accounting) — Specifies columns or values involved in relational transposition
```
FOR department IN (sales, accounting)
```

Comparison Table

Feature	PIVOT Operator	UNPIVOT Operator
Transformation Direction	Rows to columns	Columns to rows
Aggregation Requirement	Requires aggregate function like SUM	No aggregate function used
Output Layout	Wider cross-tabulated table	Narrower normalized table
NULL Values	Generated for missing intersections	Filtered out from output rows

Common Pitfalls

Mistake: Omitting the aggregate function inside the PIVOT clause. Avoid: Always wrap the value column in an aggregate function like SUM.
Mistake: Attempting to use aggregate functions like SUM inside an UNPIVOT clause. Avoid: Specify the destination value column directly without any aggregation wrapper.
Mistake: Forgetting to provide a table alias for the PIVOT or UNPIVOT expression. Avoid: Always append an AS alias clause immediately following the operator closing parenthesis.
Mistake: Expecting missing row combinations to retain existing values after pivoting. Avoid: Account for NULL values appearing in pivoted columns where no corresponding source record existed.

FAQs

Why does PIVOT require an aggregate function while UNPIVOT does not? PIVOT maps multiple rows to single cells, requiring aggregation like SUM to consolidate values. UNPIVOT unpacks distinct column cells into separate rows without combining data.
What happens to employee records that lack values for a pivoted column? The PIVOT operation automatically fills missing category intersections with NULL values in the corresponding columns.
Must the pivoted or unpivoted subquery always have an alias? Yes, SQL relational syntax requires derived tables produced by PIVOT and UNPIVOT operators to have an explicit table alias.

Common pitfalls

Mistake: Omitting the aggregate function inside the PIVOT clause. Avoid: Always wrap the value column in an aggregate function like SUM.
Mistake: Attempting to use aggregate functions like SUM inside an UNPIVOT clause. Avoid: Specify the destination value column directly without any aggregation wrapper.
Mistake: Forgetting to provide a table alias for the PIVOT or UNPIVOT expression. Avoid: Always append an AS alias clause immediately following the operator closing parenthesis.
Mistake: Expecting missing row combinations to retain existing values after pivoting. Avoid: Account for NULL values appearing in pivoted columns where no corresponding source record existed.

Hello everyone, welcome back to another video on Crack Concepts and in today's video we will be discussing PIVOT and UNPIVOT in SQL. So what is PIVOT and UNPIVOT? They are relational operators that are used to transform one table into another in order to achieve a more simpler view of table. So where the rows are getting converted to columns, it is known as PIVOT. And where the columns are getting converted to rows, that is known as UNPIVOT. As we can clearly see in this image. So conventionally we can say that the PIVOT operator converts the rows data of the table into the column data. Rows data into the column data. The UNPIVOT operator does the opposite, that is it transforms the column data based data into rows. So it will convert columns into rows. So that is the difference between PIVOT and UNPIVOT. Let's run a few queries to understand this better. So I am running this online. This is OneCompiler where you can run codes online. You do not need to install anything. You can do it on Google. So here it is. This is my table, table named employee which has three columns: employee_id, department, and salary. Now let's use PIVOT and convert the rows into columns. So let's convert the rows over here in the department column. So we have sales and accounting, let's convert this into columns. So let's write a query to have sales and accounting in the columns section. So let's do that. Select select employee_id and what is the name of the columns that I want? Sales and accounting, so I will simply write it. From what is the table name? Employee. And then I will write PIVOT. Now we have to use PIVOT function. Now, we need to summarize the data. Because we are converting rows to columns, we need to summarize our data, so we will write sum of salary. Sum of salary for department in what are the department values that we have? Sales and accounting. And let's give it an alias pivot_table. Let's run this. So as you can see over here, the columns are sales and accounting. So what we did? Select employee_id, so employee_id is selected. Sales and columns, we have sales and accounting over here. From which table? Employees, and then we are pivoting it. Pivoting on what? Like what is the value that we want in these new columns? So we are creating two new columns: sales and accounting. What is the value we need in that? We need salary, which is why we have written sum of salary for department in sales and accounting. So we will have sum of salary over here. So employee_id 1, sales, what is the salary? 10,000, which is over here. For employee_id 1, accounting, do we have anything? No, which is why we have NULL over here. For employee_id 2, we have 50,000 under accounting, under accounting, which is why over here, under accounting we have 50,000 and we have NULL in sales. For employee_id 3, sales, we have 20,000, so under the column sales we have 20,000 over here, under the column accounting we have NULL because we do not have any value for accounting for that particular employee_id. So this is PIVOT function. Now let's unpivot the same table. Let's unpivot it back to the original table. So let's just assume that this is a table. This is a table. Let's ignore the original table, let's say this is our table and now we want to unpivot it. And I will give an alias, let's say P. And now I will just consider this as a table. Let's say this whole thing, this whole thing which has alias P is a table which is returning these values, which is the case, but let's say this is a table and now I want to unpivot it. So what I'll do is I have to unpivot it to the original table. So SELECT employee_id, department, and salary from this whole thing is a table, so from table P, and now I will write UNPIVOT, UNPIVOT. Now we need to unpivot, unpivot it on what value? Salary again. So we will write salary over here. This time we will not use SUM. Salary for department in sales and accounting. And let's give it an alias. Let's run this. And as you can see over here, we have employee_id, department, salary is also there, but for some reason, it is at the rightmost section of the page, but we do have salary. And it has been converted to the original table only. So what we did was we just wrote the column names: employee_id, department, and salary from this table unpivot it, unpivot it on what values? Salary. And again the same thing: for department in sales, accounting and then we give an alias. So this is UNPIVOT function in SQL. I hope this video was helpful. And with that, I will end this video. Please do not forget to like this video, subscribe to the channel, and check out our SQL e-book, check out our other playlist. Thank you so much for watching.

Frequently asked questions

Why does PIVOT require an aggregate function while UNPIVOT does not?

PIVOT maps multiple rows to single cells, requiring aggregation like SUM to consolidate values. UNPIVOT unpacks distinct column cells into separate rows without combining data.

What happens to employee records that lack values for a pivoted column?

The PIVOT operation automatically fills missing category intersections with NULL values in the corresponding columns.

Must the pivoted or unpivoted subquery always have an alias?

Yes, SQL relational syntax requires derived tables produced by PIVOT and UNPIVOT operators to have an explicit table alias.

How was this lesson?

Your feedback helps us refine explanations and catch bugs.