MySQL按reason分组统计近12个月数据 每月作为单独列展示的实现方法

我有一张MySQL表cases，结构与示例数据如下：

person_id   reason            date
     01       abc     2021-11-23 00:00:00
     02       def     2021-11-23 00:00:00
     01       ghi     2021-12-03 00:00:00
     03       abc     2021-10-23 00:00:00
     01       def     2021-11-23 00:00:00

我需要统计每个reason在最近12个月每个月的出现次数，每个月单独作为一列展示，期望输出格式如下：

reason   2021/01  2021/02  2021/03  2021/04   ...
  abc      125      255      111      189     ...
  def      364      846      215      792     ...
  ghi      251      700      251      105     ...

目前我编写的SELECT语句返回了大量行，无法得到期望的输出，现有代码如下：

SELECT MONTH(cases.date) , cases.reason, COUNT(cases.reason) 
FROM cases
WHERE cases.date >= NOW() - INTERVAL 1 YEAR
GROUP BY MONTH(cases.date), cases.reason

后续我参考Tim的回答优化出了如下SQL查询语句，已可满足使用需求：

SELECT
    reason,
    COUNT(CASE WHEN DATE_FORMAT(date, '%Y-%m') = date_format(date_sub(now(), INTERVAL 11 MONTH), '%Y-%m') THEN 1 END) AS "11个月前",
    COUNT(CASE WHEN DATE_FORMAT(date, '%Y-%m') = date_format(date_sub(now(), INTERVAL 10 MONTH), '%Y-%m') THEN 1 END) AS "10个月前",
    COUNT(CASE WHEN DATE_FORMAT(date, '%Y-%m') = date_format(date_sub(now(), INTERVAL  9 MONTH), '%Y-%m') THEN 1 END) AS  "9个月前",
    COUNT(CASE WHEN DATE_FORMAT(date, '%Y-%m') = date_format(date_sub(now(), INTERVAL  8 MONTH), '%Y-%m') THEN 1 END) AS  "8个月前",
    COUNT(CASE WHEN DATE_FORMAT(date, '%Y-%m') = date_format(date_sub(now(), INTERVAL  7 MONTH), '%Y-%m') THEN 1 END) AS  "7个月前",
    COUNT(CASE WHEN DATE_FORMAT(date, '%Y-%m') = date_format(date_sub(now(), INTERVAL  6 MONTH), '%Y-%m') THEN 1 END) AS  "6个月前",
    COUNT(CASE WHEN DATE_FORMAT(date, '%Y-%m') = date_format(date_sub(now(), INTERVAL  5 MONTH), '%Y-%m') THEN 1 END) AS  "5个月前",
    COUNT(CASE WHEN DATE_FORMAT(date, '%Y-%m') = date_format(date_sub(now(), INTERVAL  4 MONTH), '%Y-%m') THEN 1 END) AS  "4个月前",
    COUNT(CASE WHEN DATE_FORMAT(date, '%Y-%m') = date_format(date_sub(now(), INTERVAL  3 MONTH), '%Y-%m') THEN 1 END) AS  "3个月前",
    COUNT(CASE WHEN DATE_FORMAT(date, '%Y-%m') = date_format(date_sub(now(), INTERVAL  2 MONTH), '%Y-%m') THEN 1 END) AS  "2个月前",
    COUNT(CASE WHEN DATE_FORMAT(date, '%Y-%m') = date_format(date_sub(now(), INTERVAL  1 MONTH), '%Y-%m') THEN 1 END) AS  "1个月前",
    COUNT(CASE WHEN DATE_FORMAT(date, '%Y-%m') = date_format(now(),                              '%Y-%m') THEN 1 END) AS  "本月"
FROM cases
GROUP BY reason;

内容的提问来源于stack exchange，提问作者pato