TIL a simple pattern for pivoting data with an SQL query using FILTER (or a CASE WHEN clause if the FILTER keyword isn’t supported by the database). The example input data is from the Chinook Database and is modeled like this:

| InvoiceDate         | BillingCountry | Total |
| ------------------- | -------------- | ----- |
| 2011-02-15 00:00:00 | Belgium        | 1.98  |
| 2009-10-17 00:00:00 | Brazil         | 13.86 |
| 2009-03-05 00:00:00 | USA            | 3.96  |
| 2012-09-05 00:00:00 | Czech Republic | 16.86 |
| 2012-10-27 00:00:00 | India          | 1.98  |
| 2012-08-27 00:00:00 | Hungary        | 3.96  |
| 2011-07-25 00:00:00 | Canada         | 8.91  |
| 2012-06-25 00:00:00 | USA            | 1.98  |
| 2012-02-22 00:00:00 | Poland         | 1.98  |
| 2013-03-05 00:00:00 | Italy          | 8.91  |

A pivot query using FILTER (with a SQLite db)

SELECT strftime('%Y', InvoiceDate) AS year,
 sum(Total) FILTER (WHERE BillingCountry = 'Canada') AS canada_total,
 sum(Total) FILTER (WHERE BillingCountry = 'USA') AS usa_total,
 sum(Total) FILTER (WHERE BillingCountry NOT IN ('Canada', 'USA')) AS rest_total
FROM Invoice
GROUP BY strftime('%Y', InvoiceDate)

or using CASE WHEN

SELECT strftime('%Y', InvoiceDate) AS year,
 sum(CASE WHEN BillingCountry = 'Canada' THEN Total END) AS canada_total,
 sum(CASE WHEN BillingCountry = 'USA' THEN Total END) AS usa_total,
 sum(CASE WHEN BillingCountry NOT IN ('Canada', 'USA') THEN Total END) AS rest_total
FROM Invoice
GROUP BY strftime('%Y', InvoiceDate)

both create this output:

| year | canada_total | usa_total | rest_total |
| ---- | ------------ | --------- | ---------- |
| 2009 | 57.42        | 103.95    | 288.09     |
| 2010 | 76.26        | 102.98    | 302.21     |
| 2011 | 55.44        | 103.01    | 311.13     |
| 2012 | 42.57        | 127.98    | 306.98     |
| 2013 | 72.27        | 85.14     | 293.17     |