MySQL: Group result of timestamp depending data in columns

I have data like this for a period over 3 years:

I would like to query and group the sum of consumption over a month grouped by years. Every year shall have its own column like this:

All I already managed to query was:

SELECT
    DATE_FORMAT(TimeStamp, '%M %Y'),
    sum(Consumption)
FROM
    SolarEdge_EnergyDetails
GROUP BY
    date_format(TimeStamp, '%M %Y');

resulting in this:

How do I get my results sort per year in columns?

Solution 1:

You can do it with a static query like thus, but you must change it every year

SELECT 
MONTH(ts) AS MONTH,
sum(IF(YEAR(ts) = 2020,con,NULL)) AS '2020',
sum(IF(YEAR(ts) = 2021,con,NULL)) AS '2021',
sum(IF(YEAR(ts) = 2022,con,NULL)) AS '2022'

FROM SolarEdge_EnergyDetails
GROUP BY MONTH(ts)
ORDER BY ts;

Or you change the column name to relativ like this:

SELECT 
MONTH(ts) AS MONTH,
sum(IF(YEAR(ts) = YEAR(now()),con,NULL)) AS 'this year',
sum(IF(YEAR(ts) = YEAR(now() - INTERVAL 1 YEAR),con,NULL)) AS 'last year',
sum(IF(YEAR(ts) = YEAR(now() - INTERVAL 2 YEAR),con,NULL)) AS '2 years ago'

FROM SolarEdge_EnergyDetails
WHERE YEAR(ts) >= YEAR(now() - INTERVAL 2 YEAR)
GROUP BY MONTH(ts)
ORDER BY ts;