Pivot and unpivot concepts in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a simple PIVOT query

Given the table Sales with columns Year, Product, and Amount, what is the output of this PIVOT query?

SELECT * FROM (
  SELECT Year, Product, Amount FROM Sales
) AS SourceTable
PIVOT (
  SUM(Amount) FOR Product IN ([A], [B])
) AS PivotTable
ORDER BY Year;

SQL

CREATE TABLE Sales (Year INT, Product VARCHAR(10), Amount INT);
INSERT INTO Sales VALUES
(2020, 'A', 100),
(2020, 'B', 150),
(2021, 'A', 200),
(2021, 'B', 250);

A

Year | Product | Amount
-----|---------|-------
2020 | A       | 100
2020 | B       | 150
2021 | A       | 200
2021 | B       | 250

B

Year | A   | B
-----|-----|----
2020 | 150 | 100
2021 | 250 | 200

C

Year | A   | B
-----|-----|----
2020 | NULL| NULL
2021 | NULL| NULL

D

Year | A   | B
-----|-----|----
2020 | 100 | 150
2021 | 200 | 250

💡 Hint

Remember that PIVOT aggregates values and arranges them as columns based on the specified IN list.

The PIVOT query sums Amounts for each Product per Year, turning Product values into columns A and B with their sums.

❓ query_result

intermediate

Output of an UNPIVOT query

Given the table MonthlySales with columns Year, Jan, Feb, what is the output of this UNPIVOT query?

SELECT Year, Month, Sales FROM MonthlySales
UNPIVOT (
  Sales FOR Month IN (Jan, Feb)
) AS UnpivotTable
ORDER BY Year, Month;

SQL

CREATE TABLE MonthlySales (Year INT, Jan INT, Feb INT);
INSERT INTO MonthlySales VALUES
(2020, 100, 150),
(2021, 200, 250);

A

Year | Jan | Feb
-----|-----|----
2020 | 100 | 150
2021 | 200 | 250

B

Year | Month | Sales
-----|-------|------
2020 | Jan   | 100
2020 | Feb   | 150
2021 | Jan   | 200
2021 | Feb   | 250

C

Year | Month | Sales
-----|-------|------
2020 | Jan   | 150
2020 | Feb   | 100
2021 | Jan   | 250
2021 | Feb   | 200

D

Year | Month | Sales
-----|-------|------
2020 | NULL  | NULL
2021 | NULL  | NULL

💡 Hint

UNPIVOT converts columns into rows, so each month becomes a row with its sales.

The UNPIVOT query transforms the Jan and Feb columns into rows under Month with corresponding Sales values.

🧠 Conceptual

advanced

Understanding PIVOT aggregation behavior

Why does a PIVOT operation in SQL require an aggregation function like SUM or COUNT?

A Because PIVOT combines multiple rows into one, aggregation summarizes values for each new column.

B Because PIVOT only works on single rows and does not need aggregation.

C Because aggregation is optional and only used for sorting results.

D Because PIVOT duplicates rows without changing data, so aggregation is irrelevant.

💡 Hint

Think about what happens when multiple rows share the same pivot column value.

PIVOT groups rows by the pivot column and aggregates values to produce one row per group with summarized columns.

📝 Syntax

advanced

Identify the syntax error in this PIVOT query

Which option contains a syntax error in this PIVOT query?

SELECT * FROM (
  SELECT Year, Product, Amount FROM Sales
) AS SourceTable
PIVOT (
  SUM(Amount) FOR Product IN (A, B)
) AS PivotTable;

A SUM(Amount) should be replaced with COUNT(Amount)

B The alias PivotTable is missing after PIVOT keyword

C The IN list should have column names in square brackets: [A], [B]

D The SELECT * should specify columns explicitly

💡 Hint

Check the syntax for the IN list in PIVOT clause.

In SQL Server PIVOT, the IN list must use square brackets around column names to be valid identifiers.

❓ optimization

expert

Optimizing UNPIVOT for large datasets

You have a large table with many monthly sales columns (Jan to Dec). Which approach optimizes performance when unpivoting these columns into rows?

A Use CROSS APPLY with VALUES clause to unpivot columns efficiently

B Use multiple UNION ALL queries to select each month separately and combine

C Create a normalized table with month and sales columns and join it instead of unpivoting

D Use UNPIVOT operator directly on all 12 columns in one query

💡 Hint

Consider SQL Server's CROSS APPLY with VALUES for better performance than UNPIVOT.

CROSS APPLY with VALUES is often faster and more flexible than UNPIVOT for large wide tables.