How can I write this SQL query

Question

I have a database table looks like this:

Group   -   Year  -      Value
-------------------------------
Group A     2018         200
Group A     2019         300
Group A     2020         400
Group B     2019         500
Group B     2020         300

I want to write a SQL query or something like that or a reporting tool to generate a report as below:

Group       2018     2019    2020
----------------------------------
Group A      200      300     400
Group B      ----     500     300

I tried different ways but still not sure how to do that? Anyone can help?

Answer 1

You can use conditional aggregation:

select group,
       sum(case when year = 2018 then value end) as value_2018,
       sum(case when year = 2019 then value end) as value_2019,
       sum(case when year = 2020 then value end) as value_2020
from t
group by group;

Note that group is a really bad name for a column, because it is a SQL keyword. If this is the actual name, I would suggest you change it.