Creating a Crosstab query with mysql

Question

I am new to php and mysql and I am trying to create a crosstab query using the code below:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT('SUM(IF(category = ''',category,''', duration,NULL))AS ',category)) INTO @sql

FROM tblnon_oeedata;

SET @sql = CONCAT('SELECT productionDay, ', @sql, ' FROM tblnon_oeedata GROUP BY productionDay');

PREPARE stmt FROM @sql;
EXECUTE stmt;

Everytime I run it I keep getting the error:

#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 'Maintenance During Production,SUM(IF(category = 'Breakdowns', duration,NULL))AS ' at line 1

How do I solve this problem, i am really stuck. Thanks in Advance

Answer 1

Try this:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT('SUM(IF(category = ''',category,''', duration,NULL))AS ''',category, '''')) INTO @sql

FROM tblnon_oeedata;

SET @sql = CONCAT('SELECT productionDay, ', @sql, ' FROM tblnon_oeedata GROUP BY productionDay');

PREPARE stmt FROM @sql;
EXECUTE stmt;