MySQL中的数据透视表-使用varchar格式的值转换为数据透视表

一尘不染

MySQL中的数据透视表-使用varchar格式的值转换为数据透视表

mysql

我想从Table1转换数据，如您在数据透视表中的第一张图片上所见。在MySQL中可以做到吗？因为数据透视表（A，B，C，D）的值是varchar数据格式，所以我不能使用MySQL的任何聚合函数（例如SUM或其他函数）。

Table1:
PK        Name     Subject     Grade
-------------------------------------
1         Bob       Math        A
2         Bob       History     B
3         Bob       Language    C
4         Bob       Biology     D
5         Sue       History     C
6         Sue       Math        A
7         Sue       Music       A
8         Sue       Geography   C


Pivot_table:
Subject     Bob     Sue
-------------------------
Math        A        A
History     B        C
Language    C 
Biology     D
Music                A
Geography            C

谢谢你的帮助

阅读 300

2020-05-17

共1个答案

一尘不染

静态查询（就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(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;
END$$
DELIMITER ;

用法示例：

CALL sp_grade_report();

这是 SQLFiddle 演示

2020-05-17