Using CTE to create pivot table-CodePudding

I've a table:

Task:

Create a pivot table using CTE.

Count the orders placed for each month for several years: from 2011 to 2013. The final table should include four fields: invoice_month, year_2011, year_2012, year_2013. The month field must store the month as a number between 1 and 12. If no orders were placed in any month, the number of that month should still be included in the table.

I was able to solve this task with this query:

WITH year11
AS (
    SELECT EXTRACT(MONTH FROM invoice.invoice_date::TIMESTAMP) AS invoice_month
        ,COUNT(*) AS orders
    FROM invoice
    WHERE EXTRACT(YEAR FROM invoice.invoice_date::TIMESTAMP) = 2011
    GROUP BY invoice_month
    )
    ,year12
AS (
    SELECT EXTRACT(MONTH FROM invoice.invoice_date::TIMESTAMP) AS invoice_month
        ,COUNT(*) AS orders
    FROM invoice
    WHERE EXTRACT(YEAR FROM invoice.invoice_date::TIMESTAMP) = 2012
    GROUP BY invoice_month
    )
    ,year13
AS (
    SELECT EXTRACT(MONTH FROM invoice.invoice_date::TIMESTAMP) AS invoice_month
        ,COUNT(*) AS orders
    FROM invoice
    WHERE EXTRACT(YEAR FROM invoice.invoice_date::TIMESTAMP) = 2013
    GROUP BY invoice_month
    )
SELECT year11.invoice_month
    ,year11.orders AS year_2011
    ,year12.orders AS year_2012
    ,year13.orders AS year_2013
FROM year11
INNER JOIN year12 ON year11.invoice_month = year12.invoice_month
INNER JOIN year13 ON year11.invoice_month = year13.invoice_month

But this request looks too big (or not?).

What can I improve (should I?)using CTE in my query?
Other tools to solve this task fast and beautiful?

CodePudding user response：

I find using filtered aggregation a lot easier to generate pivot tables:

SELECT extract(month from inv.invoice_date) AS invoice_month
       COUNT(*) filter (where extract(year from inv.invoice_date) = 2011) AS orders_2011,
       COUNT(*) filter (where extract(year from inv.invoice_date) = 2012) AS orders_2012,
       COUNT(*) filter (where extract(year from inv.invoice_date) = 2013) AS orders_2013
FROM invoice inv
WHERE inv.invoice_date >= date '2011-01-01'
  AND inv.invoice_date < date '2014-01-01'
GROUP BY invoice_month