0

What's wrong with this Dynamic Pivot table query? I'm staring myself blind on this.

mysql> SET @sql = NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->   GROUP_CONCAT(DISTINCT
    ->     CONCAT(
    ->       'IF(q.label = ''',
    ->       label,
    ->       ''', 1, 2) AS ',
    ->       label
    ->    )
    ->   ) INTO @sql
    -> FROM question;
Query OK, 1 row affected (0.00 sec)

mysql> SET @sql = CONCAT('SELECT a.id, ', @sql, ', q.id
    '>       FROM answer a
    '>       INNER JOIN question q ON questionId = q.id
    '>       GROUP BY a.id');
Query OK, 0 rows affected (0.01 sec)

mysql> PREPARE stmt FROM @sql;
ERROR 1064 (42000): You have an error in your SQL syntax ... near ' q.id

Why does the first one pass but the second one blows up?

Adding PREPARE stmt FROM @sql; on the GROUP_CONCAT query gives me this:

You have an error ... near 'IF(q.label = 'Q1', 1, 2) AS Q1,IF(q.label = 'Q2', 1, 2) AS Q2,IF(q.label = '', 1' at line 1

I'm not expecting the label to be empty on the last if, but I don't see how that would blow up the last query.

The query without the dynamic part returns this:

+----+-------+----+
| id | label | id |
+----+-------+----+
|  1 | Q1    |  1 |
|  2 | Q2    |  1 |
|  3 | Q1    |  1 |
|  4 | Q2    |  1 |
+----+-------+----+
3
  • Anytime you are working with Dynamic SQL, always have a debug line that shows the resulting SQL statement. Mar 4, 2020 at 18:05
  • @MichaelKutz You mean add a PREPARE stmt FROM @sql; after the GROUP_CONCAT query? I've updated my question. Mar 5, 2020 at 8:17
  • @MichaelKutz Yeah it was the null value that screwed it up. Thanks. Mar 5, 2020 at 8:49

1 Answer 1

0

This is the correct form of the if clause

SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT
     CONCAT(
       'IF(q.label = "',
       label,
       '", 1, 2) AS ',
       label
    )
   ) INTO @sql
    FROM question;
SET @sql = CONCAT('SELECT a.id, ', @sql, ', q.id
       FROM answer a
       INNER JOIN question q ON questionId = q.id
       GROUP BY a.id');
PREPARE stmt FROM @sql;

But still you should always write the result of SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT
     CONCAT(
       'IF(q.label = "',
       label,
       '", 1, 2) AS ',
       label
    )
   ) INTO @sql
    FROM question;
 SELECT @sql;
7
  • But the value of @sql is never null. Mar 5, 2020 at 8:21
  • Could you please provide the data and not only the result of your or at least make a select @sql before you concat it.
    – nbk
    Mar 5, 2020 at 8:53
  • Already updated the question. Turns out the problem was with IF(q.label = ''... Mar 5, 2020 at 9:06
  • I updated my answer
    – nbk
    Mar 5, 2020 at 9:58
  • So double quotes to make the second query not blow up on null or empty q.label values? Mar 5, 2020 at 10:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.