静态查询（就Bob和Sue而言）可能看起来像这样
SELECt subject, MAX(CASE WHEN name = 'Bob' THEN grade END) `Bob`, MAX(CASE WHEN name = 'Sue' THEN grade END) `Sue` FROM table1 GROUP BY subject
现在可以使用动态SQL来解释其他名称
SET @sql = NULL;SELECt GROUP_CONCAt(DISTINCT CONCAt('MAX(CASE WHEN name = ''', name, ''' THEN grade END) `', name, '`')) INTO @sql FROM table1;SET @sql = CONCAt('SELECt subject, ', @sql, 'FROM table1 GROUP BY subject');PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;输出：
| 主题| BOB | 苏|| ----------- || -------- | -------- || 生物学| D | （空）|| 地理| （空）| C || 历史| B | C || 语言| C | （空）|| 数学| A | A || 音乐| （空）| A |
这是 SQLFiddle 演示
您可以将其包装到存储过程中，以简化调用端的操作
DELIMITER $$CREATE PROCEDURE sp_grade_report()BEGIN SET @sql = NULL; SELECt GROUP_CONCAt(DISTINCTCONCAt('MAX(CASE WHEN name = ''', name, ''' THEN grade END) `', name, '`')) INTO @sql FROM table1; SET @sql = CONCAt('SELECt subject, ', @sql, ' FROM table1 GROUP BY subject'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;END$$DELIMITER ;用法示例：
CALL sp_grade_report();
这是 SQLFiddle 演示
