Pivot and unpivot concepts in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the PIVOT operation do in SQL?

PIVOT turns rows into columns, summarizing data to make it easier to compare values side by side.

beginner

What is the purpose of the UNPIVOT operation in SQL?

UNPIVOT turns columns into rows, helping to normalize data or prepare it for analysis.

intermediate

In a PIVOT operation, what kind of SQL clause is typically used to aggregate data?

An aggregate function like SUM(), COUNT(), or AVG() is used to combine values when turning rows into columns.

beginner

Give a simple example of when you might use UNPIVOT.

If you have sales data with columns for each month, UNPIVOT can turn those month columns into rows with a month name and sales value, making it easier to analyze trends.

beginner

Why is PIVOT useful for reporting?

PIVOT helps create summary tables that show data in a clear, column-based format, making reports easier to read and understand.

What does the PIVOT operation do in SQL?

A Joins two tables

B Turns rows into columns

C Deletes duplicate rows

D Turns columns into rows

PIVOT changes rows into columns to summarize data.

Which SQL function is commonly used with PIVOT to summarize data?

A SUM()

B JOIN()

C WHERE()

D ORDER BY()

SUM() aggregates values when pivoting data.

What is the main use of UNPIVOT in SQL?

A Convert rows into columns

B Sort data

C Filter data

D Convert columns into rows

UNPIVOT converts columns into rows for easier analysis.

If you have monthly sales as columns, which operation helps to list them as rows?

A UNPIVOT

B PIVOT

C GROUP BY

D DISTINCT

UNPIVOT turns monthly columns into rows.

Why might you use PIVOT in a report?

A To add new rows

B To delete rows

C To display data side by side in columns

D To filter data

PIVOT arranges data in columns for easy comparison.

Explain in your own words what PIVOT and UNPIVOT do in SQL and give a simple example for each.

Think about how data layout changes between rows and columns.

Why are PIVOT and UNPIVOT useful when working with data? Describe a situation where each would help.

Consider how data presentation affects understanding.