目前，我有一个班级主席的成绩表。我正在尝试将行作为列连接起来。从本质上说，调查结果应该提供不同年份班长性别的概况。
表，命名为 results :

+----+------+---------+--------+----------+-----------+-------+----------+-----------+
| id | year | faculty | winner | w_gender | w_percent | loser | l_gender | l_percent |
+----+------+---------+--------+----------+-----------+-------+----------+-----------+
|  1 | 2016 | Yellow  | Tom    | B        |        56 | Jill  | G        |        46 |
|  2 | 2016 | Green   | Susan  | G        |        52 | Sandy | G        |        48 |
|  3 | 2016 | Purple  | Carly  | G        |        51 | Jax   | B        |        49 |
|  4 | 2018 | Yellow  | Tom    | B        |        56 | Jill  | G        |        46 |
|  5 | 2018 | Green   | Ben    | B        |        52 | Sandy | G        |        48 |
|  6 | 2018 | Purple  | Amanda | G        |        52 | James | B        |        48 |
+----+------+---------+--------+----------+-----------+-------+----------+-----------+

预期结果：

+--------+------+------+
| group  | 2016 | 2018 |
+--------+------+------+
| yellow | B    | G    |
| green  | G    | G    |
| purple | G    | B    |
+--------+------+------+

工作mysql查询，从mysql修改，将多行作为列连接：

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN year = ', year, ' THEN w_gender END) AS ', CONCAT('`', year, '`')) ORDER BY year ASC) INTO @sql FROM results;
SET @sql = CONCAT('SELECT faculty, ', @sql, ' FROM results GROUP BY faculty');
PREPARE stmt FROM @sql;
EXECUTE stmt;

我当前的mysql查询太复杂，偶尔会触发mysql超时。那么，如何简化mysql查询呢？
更新：the year 列应该是动态的，即使我在未来几年添加更多结果，查询也应该可以工作。