MySQL中的数据透视表-使用varchar格式的值转换为数据透视表 [英] Pivot table in MySQL - convert to pivot table with values in varchar format

查看:87
本文介绍了MySQL中的数据透视表-使用varchar格式的值转换为数据透视表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我想转换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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆