Newbee Dev Newbee Dev - 21 days ago 5
MySQL Question

MAX(IF()) PIVOT produce error if I change my column value to string

I have this error.

When I change column

Question name
value from int ex
1
to string ex
Q1
my query will now produce an error

My first query has no problem when the values of
Question name
are
integer
data type

enter image description here


But when I change values to string like you see below it produce an error

enter image description here
I use this query

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(`Question name` = ', `Question name`, ',`mark`,NULL))')
) INTO @sql
FROM Question;

SET @sql = CONCAT('SELECT `User`.`id`, `User`.`name`, `Grade`.`grade`, ', @sql, '
FROM `User` join `Grade` on `User`.`id` = `Grade`.`user_id`
join `Question` on `User`.`id` = `Question`.`user_id` GROUP BY `User`.`id`');

PREPARE stmt FROM @sql;
EXECUTE stmt;

Answer

You forgot to add quotes this:

'MAX(IF(Question name = ', Question name, ',mark,NULL))')

should be :

'MAX(IF(Question name = "', Question name, '",mark,NULL))'