I'm trying to do a dynamic pivot in mysql

Question

I am trying to do a dynamic pivot in mysql but it is saying that a I have a syntax error, how could I correct this?:

MySql 7

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN subject = "',
      subject,'"  AND ', 
           (CASE WHEN media IS NOT NULL 
           THEN CONCAT("media = ",media) 
           ELSE media IS NULL END),
      ' THEN 1 ELSE 0 end) AS ',
      subject, IFNULL(media,'')

    )
  )
INTO @sql
FROM
  cs_media_simonline;

SET @sql = CONCAT('SELECT user_id, ', @sql, ' 
                  FROM cs_media_simonline 
                   GROUP BY user_id');

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

This the error:

'.0000000,SUM(CASE WHEN subject = "MATEMATICA" AND media = 0.0000000 THEN 1 ELSE' row 1

Is the syntax error in the procedure, or in the value of `@sql`? If the latter, can you do `SELECT @sql;` and show the result? — Barmar, Nov 21 '19 at 18:26
this the error: `'.0000000,SUM(CASE WHEN subject = "MATEMATICA" AND media = 0.0000000 THEN 1 ELSE' row 1` — Miguel Silva, Nov 21 '19 at 18:31

score 2 · Accepted Answer · answered Nov 21 '19 at 18:52

It looks like your subject resulted in a syntax error when you tried to use it as a column alias. You can't use a numeric constant as a column alias:

mysql> select 'test' as 123.000;

ERROR 1064 (42000): 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 '123.000' at line 1

So you should quote the value of subject so it doesn't do that.

I suggest using the builtin QUOTE() function, which will take care of quoting and even take care of escaping if the value is a string that contains apostrophes.

SELECT
    GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN subject = ',
      QUOTE(subject),'  AND ', 
           (CASE WHEN media IS NOT NULL 
           THEN CONCAT('media = ',media) 
           ELSE 'media IS NULL' END),
      ' THEN 1 ELSE 0 end) AS ',
      QUOTE(subject), IFNULL(media,'')

    )
  )
INTO @sql
FROM
  cs_media_simonline;

I'm trying to do a dynamic pivot in mysql

1 Answers1