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
Last activity: May 14, 2025, 10:07 AM