How to use pivot in SQL

Question

SELECT * FROM (SELECT year, amount, month FROM test) AS table1
PIVOT
    (SUM(amount) 
    for month in ('1' as m1, '2' as m2, '3' as m3, '4' as m4)) AS table2
GROUP BY year;

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sum(amount) for month in ('1' as m1, '2' as m2, '3' as m3, '4' as m4)) as ta' at line 4

What causes this error?

sample data and expected result would be helpful. Please format your code using {}. — Sujitmohanty30, Sep 05 '20 at 10:16
It seems MySQL doesn't support `pivot`. You could look into https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql/7675121#7675121 . You need to use case and aggregation instead to achieve the same. — Sujitmohanty30, Sep 05 '20 at 10:32
I would suggest you use capital letters for SQL in order to help reading your code. I suggested an edit for it. — darclander, Sep 05 '20 at 13:01

score 0 · Answer 1 · answered Sep 05 '20 at 11:09

You can do this with conditional aggregation. This is a portable syntax, that works in most databases:

select
    year,
    sum(case when month = 1 then amount else 0 end) m1,
    sum(case when month = 2 then amount else 0 end) m2,
    sum(case when month = 3 then amount else 0 end) m3,
    sum(case when month = 4 then amount else 0 end) m4
from table1
group by year

score 0 · Answer 2 · answered Sep 06 '20 at 19:44

MySQL, even version 8, doesn't support PIVOT Clause, you can prefer using Conditional Aggregation through Dynamic SQL instead, using CONCAT() and GROUP_CONCAT() functions such as

SET @sql = NULL;

SELECT GROUP_CONCAT(
             DISTINCT
             CONCAT(
                    'SUM(CASE WHEN month = ', month,' THEN AMOUNT ELSE 0 END) AS m',month
                    )
       )
  INTO @sql
  FROM tab;

SET @sql = CONCAT('SELECT year,',@sql,' FROM tab GROUP BY year'); 

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Demo

How to use pivot in SQL

2 Answers2