✅ Practice: Crosstab Salary Report in SQL

📊 Practice: Crosstab Salary Reports

In this exercise, you'll learn how to create cross-tab style salary reports using PIVOT or CASE WHEN in SQL. This helps summarize monthly or departmental data across columns.

📋 Sample employee_salary Table

employee	month	salary
Arif	Jan	50000
Arif	Feb	51000
Nasrin	Jan	60000
Nasrin	Feb	62000
Faruk	Jan	55000
Faruk	Feb	54000

🎯 Goal: Crosstab Monthly Salary Report

We want to turn months into columns for each employee.

✅ Expected Output:

employee	Jan	Feb
Arif	50000	51000
Nasrin	60000	62000
Faruk	55000	54000

1️⃣ SQL Query (MySQL / Standard SQL Using CASE)

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

2️⃣ SQL Query (Oracle PIVOT Version)

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

💡 Extra Practice Ideas

• ➕ Add more months (Mar, Apr, etc.)
• 📆 Crosstab by department vs. employee
• 📊 Show salary difference: Feb − Jan
• ⚖️ Show average salary per month

📌 Summary:
Crosstab reports are built using PIVOT or CASE WHEN with GROUP BY. Use them to create monthly or category-wise breakdowns from rows into columns.

sql crosstab report, pivot salary report sql, monthly salary table in sql, employee salary breakdown, mysql pivot using case, sql salary comparison, bangladeshi employee pivot