How to use pivot in SQL

user_6458014 0
Viewed 53
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?

pivot mysql sql
2 Answers

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
user_106767160

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

user_58413060
Related