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

Need a 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

Need a 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

Need a 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

Need a hint?

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