Dynamic Pivot Table syntax error

Question

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 |
+----+-------+----+

• Michael Kutz: Anytime you are working with Dynamic SQL, always have a debug line that shows the resulting SQL statement.
• dan-klasson: @MichaelKutz You mean add a PREPARE stmt FROM @sql; after the GROUP_CONCAT query? I've updated my question.
• dan-klasson: @MichaelKutz Yeah it was the null value that screwed it up. Thanks.

Answer 1

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;

• dan-klasson: But the value of @sql is never null.
• nbk: Could you please provide the data and not only the result of your or at least make a select @sql before you concat it.
• dan-klasson: Already updated the question. Turns out the problem was with IF(q.label = ''...
• nbk: I updated my answer
• dan-klasson: So double quotes to make the second query not blow up on null or empty q.label values?