GROUP_CONCAT mysql statement error

Question

I have tried mysql ststement for dinamically rows to column by followed here with query statement:

SET @sql = NULL;
SELECT 
GROUP_CONCAT(DISTINCT
  CONCAT(
    'MAX(CASE WHEN col = ''', 
    col,
    ''' THEN val END) as `',
    col, '`'
  )
)INTO @sql
FROM
(
   SELECT A.id_a, D.id_c id_c,
          C.students students,
          CONCAT(B.`code`, '_', A.id_a) col,
          CONCAT(D.value_m, ',', D.value_n) val
  FROM table_a A
      INNER JOIN table_d D ON A.id_a =D.id_a
      INNER JOIN table_b B ON D.id_b=B.id_b 
      INNER JOIN table_c C ON D.id_c=C.id_c
)dd;

SET @sql = CONCAT('SELECT id_c, students,', @sql, '
        FROM(
            SELECT A.id_a, D.id_c id_c,
                C.students students,
                CONCAT(B.`code`, '_', A.id_a) col,
                CONCAT(D.value_m, ',', D.value_n) val
            FROM table_a A
                INNER JOIN table_d D ON A.id_a =D.id_a
                INNER JOIN table_b B ON D.id_b=B.id_b 
                INNER JOIN table_c C ON D.id_c=C.id_c
        )dd 
        GROUP BY id_c'
    );

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

with result message:

Query OK, 0 rows affected
Query OK, 1 row affected
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '', A.id_a) col,
            CONCAT(D.value_m, ',', D.value_n) val
        FROM table_a A    ' at line 1
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MAX(CASE WHEN col = 'MAT_1' THEN val END) as `MAT_1`,MAX(CASE WHEN col = 'BIO_1'' at line 1
1243 - Unknown prepared statement handler (stmt) given to EXECUTE
1243 - Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE

I'm not familiar with sql statement like this, and what's wrong with that SQL QUERY

Thanks...

score 1 · Accepted Answer · answered Feb 17 '17 at 05:40

If you look at your code (and zoom in):

SET @sql = CONCAT('SELECT id_c, students,', @sql, '
        [..]
                CONCAT(B.`code`, '_', A.id_a) col,
                CONCAT(D.value_m, ',', D.value_n) val
        [..]
        GROUP BY id_c'
    );

you will see that _ and , are black, while they should be red as part of the string. That means your string ist "broken" there. So you need to escape the single quotes with '':

SET @sql = CONCAT('SELECT id_c, students,', @sql, '
        [..]
                CONCAT(B.`code`, ''_'', A.id_a) col,
                CONCAT(D.value_m, '','', D.value_n) val
        [..]
        GROUP BY id_c'
    );

Or use double quotes for strings that contain single quotes:

SET @sql = CONCAT('SELECT id_c, students,', @sql, "
        [..]
                CONCAT(B.`code`, '_', A.id_a) col,
                CONCAT(D.value_m, ',', D.value_n) val
        [..]
        GROUP BY id_c"
    );

Now the complete string is red as it should be :-)

http://rextester.com/SLMU41976

GROUP_CONCAT mysql statement error

1 Answers1

Linked