Pivot and unpivot concepts in SQL - Mini Project: Build & Apply

Pivot and Unpivot Concepts in SQL

📖 Scenario: You work in a small retail company. You have sales data for different products and months. The data is stored in a table with columns for product name, month, and sales amount.You want to learn how to reorganize this data to see sales per product across months in columns (pivot), and then how to revert it back to the original format (unpivot).

🎯 Goal: Build SQL queries to pivot monthly sales data by product, and then unpivot it back to the original format.

📋 What You'll Learn

Create a table called sales with columns product, month, and amount.

Insert exact sales data for three products over three months.

Write a SQL query to pivot the sales data so each product shows sales amounts in separate columns for each month.

Write a SQL query to unpivot the pivoted data back to the original three-column format.

💡 Why This Matters

🌍 Real World

Pivot and unpivot operations help transform data for reports and analysis, making it easier to compare values across categories or time periods.

💼 Career

Database analysts and developers often use pivot and unpivot queries to prepare data for dashboards, business intelligence, and decision-making.

1

Create the sales table and insert data

Create a table called sales with columns product (text), month (text), and amount (integer). Then insert these exact rows: ('Apples', 'Jan', 100), ('Apples', 'Feb', 120), ('Apples', 'Mar', 90), ('Bananas', 'Jan', 80), ('Bananas', 'Feb', 75), ('Bananas', 'Mar', 95), ('Cherries', 'Jan', 50), ('Cherries', 'Feb', 65), ('Cherries', 'Mar', 70).

SQL

-- Create the sales table
-- Insert the sales data rows

Hint

Use CREATE TABLE to define the table and INSERT INTO with multiple rows to add the data.

2

Set up month list for pivoting

Create a variable or CTE called months that lists the months 'Jan', 'Feb', and 'Mar'. This will help in pivoting the data later.

SQL

CREATE TABLE sales (
  product TEXT,
  month TEXT,
  amount INTEGER
);

INSERT INTO sales (product, month, amount) VALUES
('Apples', 'Jan', 100),
('Apples', 'Feb', 120),
('Apples', 'Mar', 90),
('Bananas', 'Jan', 80),
('Bananas', 'Feb', 75),
('Bananas', 'Mar', 95),
('Cherries', 'Jan', 50),
('Cherries', 'Feb', 65),
('Cherries', 'Mar', 70);

-- Create a CTE called months listing 'Jan', 'Feb', 'Mar'
-- Your code here

Hint

Use a WITH clause and UNION ALL to list the months.

3

Write the pivot query

Write a SQL query that pivots the sales table to show one row per product and columns for Jan, Feb, and Mar sales amounts. Use CASE statements inside SUM() to create columns Jan, Feb, and Mar.

SQL

CREATE TABLE sales (
  product TEXT,
  month TEXT,
  amount INTEGER
);

INSERT INTO sales (product, month, amount) VALUES
('Apples', 'Jan', 100),
('Apples', 'Feb', 120),
('Apples', 'Mar', 90),
('Bananas', 'Jan', 80),
('Bananas', 'Feb', 75),
('Bananas', 'Mar', 95),
('Cherries', 'Jan', 50),
('Cherries', 'Feb', 65),
('Cherries', 'Mar', 70);

WITH months AS (
  SELECT 'Jan' AS month UNION ALL
  SELECT 'Feb' UNION ALL
  SELECT 'Mar'
)

SELECT product,
  SUM(CASE WHEN month = 'Jan' THEN amount ELSE 0 END) AS Jan,
  SUM(CASE WHEN month = 'Feb' THEN amount ELSE 0 END) AS Feb,
  SUM(CASE WHEN month = 'Mar' THEN amount ELSE 0 END) AS Mar
FROM sales
GROUP BY product
-- Your code here

Hint

Use SUM(CASE WHEN month = 'Jan' THEN amount ELSE 0 END) for each month column and group by product.

4

Write the unpivot query

Write a SQL query that unpivots the pivoted sales data back to the original format with columns product, month, and amount. Use UNION ALL to combine the three month columns into rows.

SQL

CREATE TABLE sales (
  product TEXT,
  month TEXT,
  amount INTEGER
);

INSERT INTO sales (product, month, amount) VALUES
('Apples', 'Jan', 100),
('Apples', 'Feb', 120),
('Apples', 'Mar', 90),
('Bananas', 'Jan', 80),
('Bananas', 'Feb', 75),
('Bananas', 'Mar', 95),
('Cherries', 'Jan', 50),
('Cherries', 'Feb', 65),
('Cherries', 'Mar', 70);

WITH months AS (
  SELECT 'Jan' AS month UNION ALL
  SELECT 'Feb' UNION ALL
  SELECT 'Mar'
)

SELECT product,
  SUM(CASE WHEN month = 'Jan' THEN amount ELSE 0 END) AS Jan,
  SUM(CASE WHEN month = 'Feb' THEN amount ELSE 0 END) AS Feb,
  SUM(CASE WHEN month = 'Mar' THEN amount ELSE 0 END) AS Mar
FROM sales
GROUP BY product;

SELECT product, 'Jan' AS month, Jan AS amount FROM (
  SELECT product,
    SUM(CASE WHEN month = 'Jan' THEN amount ELSE 0 END) AS Jan,
    SUM(CASE WHEN month = 'Feb' THEN amount ELSE 0 END) AS Feb,
    SUM(CASE WHEN month = 'Mar' THEN amount ELSE 0 END) AS Mar
  FROM sales
  GROUP BY product
) AS pivoted
UNION ALL
SELECT product, 'Feb' AS month, Feb AS amount FROM (
  SELECT product,
    SUM(CASE WHEN month = 'Jan' THEN amount ELSE 0 END) AS Jan,
    SUM(CASE WHEN month = 'Feb' THEN amount ELSE 0 END) AS Feb,
    SUM(CASE WHEN month = 'Mar' THEN amount ELSE 0 END) AS Mar
  FROM sales
  GROUP BY product
) AS pivoted
UNION ALL
SELECT product, 'Mar' AS month, Mar AS amount FROM (
  SELECT product,
    SUM(CASE WHEN month = 'Jan' THEN amount ELSE 0 END) AS Jan,
    SUM(CASE WHEN month = 'Feb' THEN amount ELSE 0 END) AS Feb,
    SUM(CASE WHEN month = 'Mar' THEN amount ELSE 0 END) AS Mar
  FROM sales
  GROUP BY product
) AS pivoted
-- Your code here

Hint

Use UNION ALL to stack rows for each month from the pivoted data.

Practice

1. What is the main purpose of the PIVOT operation in SQL?

easy

A. To convert rows into columns for easier data summarization

B. To convert columns into rows to list data

C. To delete duplicate rows from a table

D. To join two tables based on a common column

Solution

1. Step 1: Understand what pivot does

   PIVOT changes the orientation of data by turning row values into columns.

2. Step 2: Compare with other options

   Only To convert rows into columns for easier data summarization correctly describes this; B describes unpivot, C and D are unrelated.

3. Final Answer:

   To convert rows into columns for easier data summarization -> Option A

4. Quick Check:

   Pivot = rows to columns [OK]

Hint: Pivot flips rows to columns; unpivot does the reverse [OK]

Common Mistakes:

• Confusing pivot with unpivot
• Thinking pivot deletes data
• Mixing pivot with join operations

2. Which of the following is the correct syntax to unpivot columns Q1, Q2, Q3 into rows in SQL Server?

easy

A. SELECT Product, Quarter, Sales FROM SalesData UNPIVOT (Sales IN (Q1, Q2, Q3)) AS Unpvt

B. SELECT Product, Quarter, Sales FROM SalesData UNPIVOT (Sales FOR Quarter IN ([Q1], [Q2], [Q3])) AS Unpvt

C. SELECT Product, Quarter, Sales FROM SalesData UNPIVOT (Quarter FOR Sales IN (Q1, Q2, Q3)) AS Unpvt

D. SELECT Product, Quarter, Sales FROM SalesData PIVOT (Sales FOR Quarter IN (Q1, Q2, Q3)) AS Pvt

Solution

1. Step 1: Recall UNPIVOT syntax

   UNPIVOT requires specifying the value column and the column that holds the original column names (FOR clause).

2. Step 2: Check each option

   SELECT Product, Quarter, Sales FROM SalesData UNPIVOT (Sales FOR Quarter IN ([Q1], [Q2], [Q3])) AS Unpvt correctly uses UNPIVOT (Sales FOR Quarter IN ([Q1], [Q2], [Q3])). Others misuse PIVOT or omit FOR clause.

3. Final Answer:

   SELECT Product, Quarter, Sales FROM SalesData UNPIVOT (Sales FOR Quarter IN ([Q1], [Q2], [Q3])) AS Unpvt -> Option B

4. Quick Check:

   UNPIVOT syntax = SELECT Product, Quarter, Sales FROM SalesData UNPIVOT (Sales FOR Quarter IN ([Q1], [Q2], [Q3])) AS Unpvt [OK]

Hint: UNPIVOT needs 'value FOR name IN (columns)' syntax [OK]

Common Mistakes:

• Using PIVOT instead of UNPIVOT
• Swapping value and FOR column names
• Omitting the FOR clause

3. Given the table Sales with columns Product, Year, Quarter, Amount, what will be the result of this query?

SELECT * FROM Sales PIVOT (SUM(Amount) FOR Quarter IN ([Q1], [Q2], [Q3], [Q4])) AS Pvt;

medium

A. An error because SUM cannot be used in PIVOT

B. A table with columns Product, Year, Quarter, Amount unchanged

C. A table with columns Product, Year, Q1, Q2, Q3, Q4 showing summed amounts per quarter

D. A table with columns Quarter, Product, Year, Amount

Solution

1. Step 1: Understand PIVOT with aggregation

   PIVOT with SUM aggregates Amount values grouped by Product and Year, creating columns for each Quarter.

2. Step 2: Analyze query output

   The output will have Product, Year, and one column per quarter (Q1 to Q4) with summed amounts.

3. Final Answer:

   A table with columns Product, Year, Q1, Q2, Q3, Q4 showing summed amounts per quarter -> Option C

4. Quick Check:

   PIVOT with SUM aggregates rows into columns [OK]

Hint: PIVOT with SUM groups and sums data into new columns [OK]

Common Mistakes:

• Expecting original rows unchanged
• Thinking PIVOT cannot use aggregation
• Confusing column order in output

4. You wrote this query to unpivot sales data:

SELECT Product, Quarter, Sales FROM SalesData UNPIVOT (Sales FOR Quarter IN (Q1, Q2, Q3)) AS Unpvt;

But it returns an error. What is the likely cause?

medium

A. The FOR clause should be after the IN clause

B. UNPIVOT cannot be used with more than two columns

C. The alias 'Unpvt' is missing parentheses

D. The column names in IN clause must be enclosed in square brackets

Solution

1. Step 1: Check syntax for column names in UNPIVOT

   In SQL Server, column names in the IN list must be enclosed in square brackets if they are identifiers like Q1, Q2, Q3.

2. Step 2: Identify error cause

   Missing brackets cause syntax error; other options are incorrect syntax rules.

3. Final Answer:

   The column names in IN clause must be enclosed in square brackets -> Option D

4. Quick Check:

   Use brackets for column names in UNPIVOT IN list [OK]

Hint: Always bracket column names in UNPIVOT IN list [OK]

Common Mistakes:

• Omitting brackets around column names
• Misplacing FOR clause
• Incorrect alias syntax

5. You have a table MonthlySales with columns Store, Jan, Feb, Mar representing sales per month. You want to create a report listing Store, Month, Sales rows. Which SQL approach correctly unpivots this data?

hard

A. Use UNPIVOT: SELECT Store, Month, Sales FROM MonthlySales UNPIVOT (Sales FOR Month IN ([Jan], [Feb], [Mar])) AS Unpvt;

B. Use PIVOT: SELECT Store, Month, Sales FROM MonthlySales PIVOT (SUM(Sales) FOR Month IN ([Jan], [Feb], [Mar])) AS Pvt;

C. Use JOIN to combine Jan, Feb, Mar columns into rows

D. Use GROUP BY Store, Month without unpivot

Solution

1. Step 1: Identify the goal

   We want to transform columns Jan, Feb, Mar into rows with Month and Sales columns.

2. Step 2: Choose correct SQL operation

   UNPIVOT converts columns into rows; Use UNPIVOT: SELECT Store, Month, Sales FROM MonthlySales UNPIVOT (Sales FOR Month IN ([Jan], [Feb], [Mar])) AS Unpvt; uses correct syntax for this.

3. Step 3: Evaluate other options

   PIVOT aggregates rows into columns (wrong direction), JOIN is complex and unnecessary, GROUP BY alone doesn't reshape columns.

4. Final Answer:

   Use UNPIVOT: SELECT Store, Month, Sales FROM MonthlySales UNPIVOT (Sales FOR Month IN ([Jan], [Feb], [Mar])) AS Unpvt; -> Option A

5. Quick Check:

   Unpivot columns to rows = Use UNPIVOT: SELECT Store, Month, Sales FROM MonthlySales UNPIVOT (Sales FOR Month IN ([Jan], [Feb], [Mar])) AS Unpvt; [OK]

Hint: Unpivot columns to rows using UNPIVOT with FOR and IN [OK]

Common Mistakes:

• Using PIVOT instead of UNPIVOT
• Trying to reshape with GROUP BY only
• Overcomplicating with JOINs