pivot query doesn't execute on mysql

Question

in reference to this [pivot article] I managed to get this prepared pivot query

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
    CONCAT(
      'count(IF(name = ''',
     name,
      ''', 1, NULL)) AS ',
      name
    )
  ) INTO @sql
FROM bundles;
SET @sql = CONCAT('SELECT ', @sql, ' FROM bundles');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

and here's a live demo SQLfiddle

the problem is when I try to execute that on my data with the same schema using mysql(5.6.14 Win32 x86) I get an error.

Error
SQL query:

PREPARE stmt FROM @sql ;

MySQL said:

#1064 -

just an error code but no message..
I've read SO questions like this & this but the answers are the static way which won't work with unknown columns
first of all, is this even available in mysql?? .. any pointers are appreciated

Perhaps `SELECT DISTINCT name FROM bundles` would return a single-quote/apostrophe or other undesirable character? Here's a counter-fiddle: http://sqlfiddle.com/#!2/8276f/1 which produces a similar error. — , Jul 09 '14 at 18:06
@ebyrob actually I get the error when I execute the procedure — Katia, Jul 09 '14 at 18:31
My point was, I think you may have a single quote `'` in that `name` column in `bundles`. That would certainly invalidate the syntax of the dynamic query you're building. — , Jul 09 '14 at 19:36

score 1 · Accepted Answer · answered Jul 09 '14 at 21:07

Your schema may be the same, but your data is probably different. You may have a keyword, space, or something else in your values which is causing the issue. Try to wrap your alias in double quotes.

Here's my edit to your SQLFiddle. I added a line which outputs the dynamic SQL in case you need to examine what you get on your system with your data.

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'count(IF(name = ''',
     name,
      ''', 1, NULL)) AS "',
      name, '"'
    )
  ) INTO @sql
FROM bundles;
SET @sql = CONCAT('SELECT ', @sql, ' FROM bundles');
SELECT CONCAT(@sql);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

pivot query doesn't execute on mysql

1 Answers1