MySQL Query to use to change table orientation (vertical to horizontal)

Question

So I have this table:

Year | MonthInterval | RainfallIndex
2016 | Jan-Feb       | 1.03
2016 | Feb-Mar       | 0.23
2016 | Mar-Apr       | 2.30

What query can I apply to transform the table into this:

Year | Jan-Feb | Feb-Mar | Mar-Apr 
2016 | 1.03    | 0.23    | 2.30

https://stackoverflow.com/questions/16568228/how-to-transpose-mysql-table-rows-into-columns — Rehan Azher, Apr 01 '18 at 14:12

score 1 · Answer 1 · answered Apr 01 '18 at 15:12

You can use a case statement:

   CREATE TABLE rainfall_pivot AS
  SELECT
    Year,
    sum(CASE WHEN MONTH(MonthInterval) = 'Jan-Feb'
      THEN RainfallIndex
        ELSE 0 END) AS "Jan-Feb",
    sum(CASE WHEN MONTH(MonthInterval) = 'Feb-Mar'
      THEN RainfallIndex
        ELSE 0 END) AS "Feb-Mar",
    sum(CASE WHEN MONTH(MonthInterval) = 'Mar-Apr'
      THEN RainfallIndex
        ELSE 0 END) AS "Mar-Apr"
  FROM your_table
  GROUP BY 1
  ORDER BY 1;

MySQL Query to use to change table orientation (vertical to horizontal)

1 Answers1