MySQL中的数据透视表-使用varchar格式的值转换为数据透视表 [英] Pivot table in MySQL - convert to pivot table with values in varchar format
问题描述
我想转换Table1中的数据,如您在数据透视表中数据的第一张图片上所看到的.可以在MySQL中做到吗?因为数据透视表(A,B,C,D)的值都是varchar数据格式,所以我无法使用MySQL的任何聚合函数(例如SUM或其他函数).
i would like convert data from Table1 as you can see on first picture on data in Pivot_table. Is possible to do that in MySQL? Because the values of pivot table(A, B, C, D) are in varchar data format and i cannot use any aggregation function of MySQL like SUM or others.
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
感谢您的帮助
推荐答案
静态查询(就Bob和Sue而言)可能看起来像这样
A static query (as far as only Bob and Sue are concerned) might look like this
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来解释其他名称
Now to be able to account for other names use dynamic SQL like this
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;
输出:
| SUBJECT | BOB | SUE |
|-----------|--------|--------|
| Biology | D | (null) |
| Geography | (null) | C |
| History | B | C |
| Language | C | (null) |
| Math | A | A |
| Music | (null) | A |
这里是 SQLFiddle 演示
Here is SQLFiddle demo
您可以将其包装到存储过程中,以简化调用端的操作
You can wrap it into a stored procedure to simplify things on the calling end
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 演示
Here is SQLFiddle demo
这篇关于MySQL中的数据透视表-使用varchar格式的值转换为数据透视表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!