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 '20 at 18:05
  • @MichaelKutz You mean add a PREPARE stmt FROM @sql; after the GROUP_CONCAT query? I've updated my question. Mar 5 '20 at 8:17
  • @MichaelKutz Yeah it was the null value that screwed it up. Thanks. Mar 5 '20 at 8:49
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 '20 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 '20 at 8:53
  • Already updated the question. Turns out the problem was with IF(q.label = ''... Mar 5 '20 at 9:06
  • I updated my answer
    – nbk
    Mar 5 '20 at 9:58
  • So double quotes to make the second query not blow up on null or empty q.label values? Mar 5 '20 at 10:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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