动态数据透视表MySql [英] Dynamic Pivot Table MySql

问题描述

我在数据库中有一个表，记录着学生的成绩，如下所示:

I have a table in a database that records student marks like so:

| id     | criteriaid   |  mark     | studentid     |
| 1      | 5            |  62       | 5             |
| 2      | 6            |  54       | 5             |
| 3      | 7            |  48       | 5             |

然后将其链接到Criteria表，如下所示:

This then links to a Criteria table like so:

| id     | title        |
| 5      | Presentation |
| 6      | Communication|
| 7      | Research     |

还有一个像这样的学生桌:

And a student table like so:

| id     |firstname     | lastname       |
| 10     |Joe           | Bloggs         |

虽然我将表连接在一起没有问题，但是看起来像这样:

While I have no issue in joining the tables together, which may look like this:

| id | firstname | lastname  |  criteria     | mark     |
| 10 | Joe       | Bloggs    |  Presentation | 62       |
| 10 | Joe       | Bloggs    |  Communication| 54       |
| 10 | Joe       | Bloggs    |  Research     | 48       |

我一直在试验数据透视表教程，但无法获得所需的结果.我认为这是一个动态数据透视表. (条件必须是动态的).这就是我想要的:

I have been experimenting with pivot table tutorials but cannot get the result I want.. which I believe is a dynamic pivot table. (the criteria needs to be dynamic). This is what I want:

| id | firstname | lastname  |  Presentation | Communication | Research  |
| 10 | Joe       | Bloggs    |       62      |      54       |     48    |

此刻，我正尝试按照此处的建议进行硬编码 mysql数据透视表出现问题

At the moment I am trying to do this hard-coded as advised here trouble with mysql pivot table

SELECT s.firstname, s.lastname, m.mark,
  max(case when c.title = 'Attitude' then m.mark end) attitude,
  max(case when c.title = 'Acting' then m.mark end) acting,
  max(case when c.title = 'Presentation' then m.mark end) presentation,
  max(case when c.title = 'Voice' then m.mark end) voice
from marks m
LEFT JOIN criteria c ON m.criteriaid = c.id
LEFT JOIN students s ON s.id = m.studentid

现在运行良好，但是我正在寻求动态解决方案.我将继续尝试-如果有人可以帮助，我们将不胜感激.

This is now working well, however I'm after a dynamic solution. I'll continue trying - if anyone can help it will be still appreciated.

我现在到这里了

    SET @sql = NULL;
    SELECT
      GROUP_CONCAT(DISTINCT
        CONCAT(
          'max(case when c.title = ''',
          title,
          ''' then m.mark end) AS ',
          replace(title, ' ', '')
        )
      ) INTO @sql
    from criteria;

    SET @sql = CONCAT('SELECT s.firstname, s.lastname,
    ', @sql,'
    from marks m
    LEFT JOIN criteria c ON m.criteriaid = c.id
    LEFT JOIN students s ON s.id = m.studentid
    group by s.id;');

    PREPARE stmt FROM @sql;
    EXECUTE stmt;

    DEALLOCATE PREPARE stmt;

这可能有效，但是在PHPMyAdmin中不起作用...正在寻找解决方案！

This may work however not working in PHPMyAdmin... looking for a solution!

推荐答案

这是有效的结果，但是在PHP中不起作用.决定与PHP一起使用硬编码选项来构建SQL字符串.

This was the working result, however does not work in PHP. Have decided to use the hard coded option along with PHP to build the SQL string.

SET @sql = NULL;
    SELECT
      GROUP_CONCAT(DISTINCT
        CONCAT(
          'max(case when c.title = ''',
          title,
          ''' then m.mark end) AS ',
          replace(title, ' ', '')
        )
      ) INTO @sql
    from criteria;

    SET @sql = CONCAT('SELECT s.firstname, s.lastname,
    ', @sql,'
    from marks m
    LEFT JOIN criteria c ON m.criteriaid = c.id
    LEFT JOIN students s ON s.id = m.studentid
    group by s.id;');

    PREPARE stmt FROM @sql;
    EXECUTE stmt;

    DEALLOCATE PREPARE stmt;

这篇关于动态数据透视表MySql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！