0

I am quite a new person to SQL, so if you can advice me where I should read more about my question, I would be very thankful. Consider I want to make a table where rows are countries and columns are years with the data on some numbers related to this year:

SELECT
  country,
  SUM(CASE year
      WHEN 2019 THEN number
      ELSE 0) AS '2019',
  SUM(CASE year
      WHEN 2020 THEN number
      ELSE 0) AS '2020',
  SUM(CASE year
      WHEN 2021 THEN number
      ELSE 0) AS '2021',
  SUM(CASE year
      WHEN 2022 THEN number
      ELSE 0) AS '2022'
FROM
  table
GROUP BY
  country

So, my question is: can I avoid manually writing all these year columns and use some kind of a loop or any other structure/statement? In some situations you may need to have the data on so many columns that it would take very long time to manually write all these logic.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
Mykyta Abramenko
  • 5
  • 2
  • MySql does not support any dynamic pivoting functionality. What you have is the way to go. – forpas Aug 13 '22 at 10:49
  • 4
    Check the answer for implementing dynamic pivot in MySQL https://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns – Ergest Basha Aug 13 '22 at 11:00

0 Answers0