Using CASE to transform a result set from multiple rows into a summary row

 Consider the situation where sales data is stored in a table by month. One row is stored per month with a table structure that looks like the following:

CREATE TABLE prodsales
 (product char(3),
  mnth    smallint,
  sales   money)

In this table, sales amounts (sales) are stored by month (mnth) and product code (product). The mnth column stores an integer value ranging from 1 (for January) to 12 (for December). You can use the following single SQL statement to product one row per product with 12 totals, one for each month:

SQL Statement will go like this

SELECT product,
  SUM(CASE mnth WHEN 1 THEN sales ELSE NULL END) AS jan,
  SUM(CASE mnth WHEN 2 THEN sales ELSE NULL END) AS feb,
  SUM(CASE mnth WHEN 3 THEN sales ELSE NULL END) AS mar,
  SUM(CASE mnth WHEN 4 THEN sales ELSE NULL END) AS apr,
  SUM(CASE mnth WHEN 5 THEN sales ELSE NULL END) AS may,
  SUM(CASE mnth WHEN 6 THEN sales ELSE NULL END) AS jun,
  SUM(CASE mnth WHEN 7 THEN sales ELSE NULL END) AS jul,
  SUM(CASE mnth WHEN 8 THEN sales ELSE NULL END) AS aug,
  SUM(CASE mnth WHEN 9 THEN sales ELSE NULL END) AS sep,
  SUM(CASE mnth WHEN 10 THEN sales ELSE NULL END) AS oct,
  SUM(CASE mnth WHEN 11 THEN sales ELSE NULL END) AS nov,
  SUM(CASE mnth WHEN 12 THEN sales ELSE NULL END) AS dec
FROM prodsales
GROUP BY product  

This statement will generates a row for each product with twelve monthly sales totals. The CASE expression causes the sales amount to by added to the appropriate bucket by checking the mnth column. If the month value is for the appropriate “month bucket”, then the sales amount is added using SUM; if not, then NULL is specified, thereby avoiding adding anything to the SUM. Using CASE expressions in this manner simplifies aggregation and reporting. It provides a quick way of transforming normalized data structures into the more common denormalized formats that most business users are accustomed to viewing on reports.

Happy Coding :)