💡 SQL PIVOT & UNPIVOT with Sales Data

🔄 SQL PIVOT & UNPIVOT Tutorial

PIVOT rotates row data into columns. UNPIVOT does the opposite — it turns columns into rows. This is very helpful for reports and data summaries.

📋 Sample sales Table

Let’s assume this table contains monthly sales by employee:

employee	month	amount
Arif	Jan	10000
Arif	Feb	12000
Nasrin	Jan	9000
Nasrin	Feb	11000

📌 Goal: Pivot months into columns

Expected Output:

employee	Jan	Feb
Arif	10000	12000
Nasrin	9000	11000

1️⃣ PIVOT (Oracle Style)

SELECT *
FROM (
  SELECT employee, month, amount
  FROM sales
)
PIVOT (
  SUM(amount)
  FOR month IN ('Jan' AS Jan, 'Feb' AS Feb)
);

2️⃣ UNPIVOT (Convert columns to rows)

If your table looks like the pivoted format, and you want to turn it back:

employee	Jan	Feb
Arif	10000	12000
Nasrin	9000	11000

Use this UNPIVOT query:

SELECT employee, month, amount
FROM sales_pivoted
UNPIVOT (
  amount FOR month IN (Jan, Feb)
);

💡 Notes:

• PIVOT works in Oracle and some advanced systems like SQL Server.
• In MySQL, you use CASE WHEN + GROUP BY to simulate pivoting.

Manual Pivot for MySQL:

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

📌 Summary:

• PIVOT: Rotates rows into columns
• UNPIVOT: Rotates columns into rows
• Use CASE WHEN method in MySQL to simulate pivoting

SQL pivot example, unpivot in oracle, mysql pivot using case, sales data analysis SQL, Bangladeshi employee sales pivot, how to pivot table sql, unpivot example query