MySql pivot table for scatter chart I would like to transform the following sql (e.g. select Name,X,Y from mytable) result:

Name  | X   | Y
bla1  | 0.1 | 0.2
bla2  | 0.3 | 0.4


Into the following form that is displayed correctly in an Excel scatter chart (i.e. with each row as a series and each name as a label):

Name  | 0.1 | 0.3
bla1  | 0.2 | 
bla2  |     | 0.4


This is the only way a scatter chart will display x-y data correctly without fiddling with chart settings:
Eventually I figured this out with help from this stack overflow post.

    SELECT CONCAT(
      'SELECT `mytable`.Name', GROUP_CONCAT(CONCAT('
         ,    `t_', REPLACE(Y, '`', '``'), '`.X
             AS `', REPLACE(Y, '`', '``'), '`'
       ) SEPARATOR ''),
     ' FROM `mytable` ', GROUP_CONCAT(CONCAT('
         LEFT JOIN `mytable`   AS `t_', REPLACE(Y, '`', '``'), '`
                ON `mytable`.Name = `t_', REPLACE(Y, '`', '``'), '`.Name
               AND `t_', REPLACE(Recall, '`', '``'), '`.Y= ', QUOTE(Recall)
       ) SEPARATOR ''),
     ' GROUP BY `mytable`.Name'
    ) INTO @qry FROM (SELECT Recall FROM `mytable` WHERE ... ) t;
    PREPARE stmt FROM @qry;
    EXECUTE stmt;


Initially this was not working because the datatype of columns X and Y was FLOAT which I suspect was messing up the conversion to string. I changed the type to be DECIMAL(10,9) (i.e. a single digit, followed by up to nine decimal points), which made the code work.