Sample Header Ad - 728x90

Dynamic Pivot Table syntax error

0 votes
1 answer
264 views
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 |
+----+-------+----+
Asked by dan-klasson (101 rep)
Mar 4, 2020, 12:43 PM
Last activity: May 14, 2025, 10:07 AM