Build dynamic pivot query in MySQL

Question

I have this query which has the following output:

SELECT o.date      AS Date, 
       u.firstname AS FirstName, 
       u.lastname  AS LastName, 
       m.name      AS MealName 
FROM   catering.user u 
       LEFT JOIN catering.order o 
              ON u.iduser = o.iduser 
       LEFT JOIN catering.meal m 
              ON m.idmeal = o.idmeal 
WHERE  o.weeknumber = 37 
GROUP  BY u.firstname, 
          o.date 
ORDER  BY o.date; 

What I need is to write a query to get the following output:

I followed the other posts related with the dynamic pivot query, but MySql is a new topic for me and I can't figure out how to use the prepared statement in my case?

The error message i get is:

PREPARE stmt FROM @sql  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 'NULL' at line 1    

I am using MySql Workbench 6.0 CE

Answer 1

SET SESSION group_concat_max_len = 1000000;

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN firstname= ''',
      firstname,
      ''' THEN mealName END) AS ',
      firstname
    )
   )INTO @sql
FROM  
(SELECT o.date      AS Date, 
       u.firstname AS FirstName, 
       u.lastname  AS LastName, 
       m.name      AS MealName 
FROM   catering.user u 
       LEFT JOIN catering.order o 
              ON u.iduser = o.iduser 
       LEFT JOIN catering.meal m 
              ON m.idmeal = o.idmeal 
WHERE  o.weeknumber = 37 
GROUP  BY u.firstname, 
          o.date 
ORDER  BY o.date)x;



SET @sql=CONCAT('SELECT date,',@sql,' FROM
(SELECT o.date      AS Date, 
       u.firstname AS FirstName, 
       u.lastname  AS LastName, 
       m.name      AS MealName 
FROM   catering.user u 
       LEFT JOIN catering.order o 
              ON u.iduser = o.iduser 
       LEFT JOIN catering.meal m 
              ON m.idmeal = o.idmeal 
WHERE  o.weeknumber = 37 
GROUP  BY u.firstname, 
          o.date 
ORDER  BY o.date)x  
GROUP BY date');


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

FIDDLE