SQL query to change rows into columns based on the aggregation from rows

Question

I have a table, "MyTable" as below:

tool	team	metric	date
tool1	team1	25	1/1/2023
tool1	team1	10	2/1/2022
tool2	team2	20	1/2/2022

I want to aggregate "metric" periodically by years (distinct years as columns) and have the sql query return something like this where value of 'year' column would be the sum of 'metric'

tool	team	2023	2022
tool1	team1	25	10
tool2	team2	0	20

Can anyone help me such a query? For the cases, I need to first infer distinct years from the table, and then use that. And this list of distinct years needs to be from a time range. For this example, we could have time range denoted like this: WHERE date between ago(600d) and now(). That 600 value I will receive from the application, that's why the years are not fixed.

I have tried the to use 'pivot' but its not supported in the database engine I am using.

Answer 1

Even though it is possible, I suggest you do this kind of operation in the application layer. If you insist, you can use a conditional aggregate for each year:

SELECT tool, team
     , COUNT(CASE YEAR(date) WHEN 2023 THEN 1 END)
     , COUNT(CASE YEAR(date) WHEN 2022 THEN 1 END)
     , ...
FROM ...
GROUP BY tool, team

Also, avoid using reserved words as identifiers. Use identifiers that describes the attribute, your date column is probably a date of something in particular.

EDIT new requirements:

SELECT tool, team
     , COALESCE(SUM(CASE YEAR(dt) WHEN 2023 THEN metric END), 0) AS y2023
     , COALESCE(SUM(CASE YEAR(dt) WHEN 2022 THEN metric END), 0) AS y2022
FROM t
GROUP BY tool, team

Fiddle