Write a Query to Pivot Payments by Month
Write a Query to Pivot Payments by Month
Customers Table:
Query Explanation :
-
The purpose of this query is to display each customer’s payments for January, February, and March in a single row.
This is called pivoting — transforming rows into columns. -
The
Customerstable is included in the query and given an aliasc.
This allows us to refer to it easily usingc. -
The
Paymentstable is joined using aLEFT JOINwith theCustomerstable.
This ensures that even customers who have not made any payments still appear in the result. -
The
SELECTclause usesSUM(CASE WHEN ... THEN ... ELSE ... END)three times — once for each month:-
If the row’s
PaymentMonth = 'January', then include itsAmount. -
Otherwise, use
0. -
The same logic applies for 'February' and 'March'.
-
-
SUM(...)is used because a customer may have made multiple payments in a single month.
UsingSUMtotals up all payments for that month per customer. -
GROUP BY c.CustomerNameensures that each customer’s name appears only once in the result,
and the total payment for each month is displayed beside their name.
SQL Query:
SELECT
c.CustomerName,
SUM(CASE WHEN p.PaymentMonth = 'January' THEN p.Amount ELSE 0 END) AS January,
SUM(CASE WHEN p.PaymentMonth = 'February' THEN p.Amount ELSE 0 END) AS February,
SUM(CASE WHEN p.PaymentMonth = 'March' THEN p.Amount ELSE 0 END) AS March
FROM Customers c
LEFT JOIN Payments p
ON c.CustomerID = p.CustomerID
GROUP BY c.CustomerName;
Output:
