Create Monthly Revenue Report for Each Department

DEPARTMENT_REVENUE

+------------+---------+-------+
| Department | Revenue | Month |
+------------+---------+-------+
| A          | 8000    | Jan   |
| B          | 9000    | Jan   |
| C          | 10000   | Feb   |
| A          | 7000    | Feb   |
| A          | 6000    | Mar   |
+------------+---------+-------+

Scenario

Finance analytics team needs a report that shows each department in one row with separate revenue columns for every month of the year.

Question

Given table DEPARTMENT_REVENUE(Department, Revenue, Month), write an SQL query that pivots the data so the output has one row per department and twelve columns: Jan_Revenue, Feb_Revenue, …, Dec_Revenue.

Hints

Use conditional aggregation (CASE) or database-specific PIVOT operator.

Pivot Monthly Revenues With Annual Total

The finance analytics team needs a report that shows each department in one row with separate revenue columns for every month of the year and an annual total.

Given table DEPARTMENT_REVENUE(Department, Revenue, Month), where Month is a three-letter abbreviation from 'Jan' to 'Dec', write an SQL query that pivots the data so the output has one row per department and twelve columns: Jan_Revenue, Feb_Revenue, …, Dec_Revenue. Months with no revenue for a department should appear as 0. Also include an Annual_Total column that is the sum of that department's revenue across all months.

Table DEPARTMENT_REVENUE

column	type	constraints
Department	VARCHAR(10)
Revenue	INTEGER
Month	VARCHAR(3)

Sample data:

Department	Month	Revenue
A	Jan	8000
B	Jan	9000
C	Feb	10000
A	Feb	7000
A	Mar	6000

Expected output

One row per department with revenue columns for each month (Jan through Dec). Months with no revenue should be shown as 0. Include an Annual_Total column summing all revenue for that department.

Annual_Total	Apr_Revenue	Aug_Revenue	Dec_Revenue	Department	Feb_Revenue	Jan_Revenue	Jul_Revenue	Jun_Revenue	Mar_Revenue	May_Revenue	Nov_Revenue	Oct_Revenue	Sep_Revenue
21000	0	0	0	A	7000	8000	0	0	6000	0	0	0	0
9000	0	0	0	B	0	9000	0	0	0	0	0	0	0
10000	0	0	0	C	10000	0	0	0	0	0	0	0	0

Hints

1. Use conditional aggregation with SUM and CASE to build each month column.
2. Use ELSE 0 in the CASE expression (or wrap SUM with COALESCE) so missing months appear as 0.
3. Annual_Total can be computed as SUM(Revenue) per Department.