CODEWITHSUNDEEP
mysqlsql
Katia
Katia

Reputation: 729

pivot query doesn't execute on mysql

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

Upvotes: 1

Views: 278

Answers (1)

Starson Hochschild
Starson Hochschild

Reputation: 754

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;

Upvotes: 1

Related Questions