我从MySQL数据库中获取了以下数据：

+------------+------------+----------------+
| competency | totalscore | date_evaluated |
+------------+------------+----------------+
|     A      |      0     |     null       |
|     B      |      0     |     null       |
|     C      |      0     |     null       |
|     D      |      0     |     null       |
|     E      |      5     |   2018-03-01   |
|     F      |      4     |   2018-03-01   |
|     E      |      4     |   2018-03-02   |
|     F      |      3     |   2018-03-02   |
+------------------------------------------+

从两个单独的表中检索

+-----------------+
|  competencies   | 
+-----------------+
|  competency_ID  |
|  competency     |
+-----------------+
+-----------------+
|  scoring_self   | 
+-----------------+
|  student_ID     |
|  competency_ID  |
|  score          |
|  date_evaluated |
+-----------------+

我希望在.aspx页上以以下形式显示：

+----------------+-------+-------+-------+-------+-------+--------+
| Date Evaluated |   A   |   B   |   C   |   D   |   E   |   F    |
+----------------+-------+-------+-------+-------+-------+--------+
| 2018-03-01     |   0   |   0   |   0   |   0   |   5   |   4    |
| 2018-03-02     |   0   |   0   |   0   |   0   |   4   |   3    |
+-----------------------------------------------------------------+

如果用户尚未尝试，则分数为0，评估日期为空。我尝试创建一个动态透视表，因为能力的数量可以根据主题的不同而变化，使用以下准备好的语句。

SET @mysql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT('(CASE WHEN score IS NOT NULL THEN score ELSE 0 END) AS `',
competency, '`')) INTO @mysql
FROM (SELECT competency, scoring_self.score FROM competency LEFT JOIN
scoring_self ON competency.competency_ID = scoring_self.competency_ID
ORDER BY competency) AS sub;
SET @mysql2= CONCAT('SELECT DISTINCT(DATE(ss.date_evaluated)) AS `Date
Evaluated`, ' , @mysql, 
'FROM competency c INNER JOIN scoring_self ss
ON c.competency_ID = ss.competency_ID
GROUP BY ss.date_evaluated;');
PREPARE stmt FROM @mysql2;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

然而，我只设法得到它在下面的形式：

+----------------+-------+-------+-------+-------+-------+--------+
| Date Evaluated |   A   |   B   |   C   |   D   |   E   |   F    |
+----------------+-------+-------+-------+-------+-------+--------+
| 2018-03-01     |   5   |   5   |   5   |   5   |   5   |   5    |
| 2018-03-02     |   4   |   4   |   4   |   4   |   4   |   4    |
+-----------------------------------------------------------------+

它似乎只是从第一次约会的第一组分数，我不太清楚是什么出了问题。任何帮助将不胜感激！
另外，我不太确定在准备好的语句中何处放置WHERE子句，以便只检索单个学生的分数。

EDIT：我已经尝试了jQuery建议的更改，并做了一些自己的调整：

SELECT
    GROUP_CONCAT(DISTINCT
                     CONCAT('(CASE WHEN score IS NOT NULL THEN score ELSE 0 END) AS `', competency, '`')) INTO @mysql
FROM 
    (SELECT competency 
     FROM competency 
     LEFT JOIN scoring_self ON competency.competency_ID = scoring_self.competency_ID 
                            AND scoring_self.student_ID = <redacted>
     GROUP BY DATE(scoring_self.date_evaluated), competency) AS sub;

这将返回以下输出：

+----------------+-------+-------+-------+-------+-------+--------+
| Date Evaluated |   A   |   B   |   C   |   D   |   E   |   F    |
+----------------+-------+-------+-------+-------+-------+--------+
| 2018-03-01     |   5   |   5   |   5   |   5   |   5   |   5    |
| 2018-03-01     |   4   |   4   |   4   |   4   |   4   |   4    |
| 2018-03-02     |   4   |   4   |   4   |   4   |   4   |   4    |
| 2018-03-02     |   3   |   3   |   3   |   3   |   3   |   3    |
+-----------------------------------------------------------------+

看起来分数并没有Map到他们应该Map的列，原因我无法理解。
编辑2：

已解决由jQuery提供

最终的预准备语句应如下所示

SET @mysql_dynamic = (
    SELECT
        GROUP_CONCAT( DISTINCT
            CONCAT(
                 'MAX(CASE WHEN competency = ''',
      competency,
      ''' AND competency IS NOT NULL THEN score ELSE 0 END) AS `',
      competency,
      '`'
            )
        )
    FROM (SELECT competency, scoring_self.score, student_ID FROM competency LEFT JOIN
scoring_self ON competency.competency_ID = scoring_self.competency_ID 
ORDER BY competency) AS sub
);
SET @mysql = CONCAT('SELECT ss.date_evaluated, ', @mysql_dynamic, ' 
FROM (SELECT date_evaluated, competency, scoring_self.score, scoring_self.student_ID FROM competency LEFT JOIN
scoring_self ON competency.competency_ID = scoring_self.competency_ID 
ORDER BY competency) AS ss WHERE ss.date_evaluated IS NOT NULL AND ss.student_ID = \'',<redacted>,'\' GROUP BY date_evaluated DESC');
PREPARE stmt FROM @mysql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

以上提供了可按学生ID筛选的所需结果。