How can I simplify MySQL join rows as columns?

Question

Currently, I have a table of class president results. I am trying to join rows as columns. Essentially, the result should provide an overview of the gender of the class president over different years.

Table, named 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 |
+----+------+---------+--------+----------+-----------+-------+----------+-----------+

Intended result:

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

Working MySQL query, modified from MySQL Join Multiple Rows as Columns:

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;

My current MySQL query is too complicated and occasionally triggers a MySQL timeout. So, how can I simplify this MySQL query?

UPDATE: The year column should be dynamic and the query should work even if I add more results in future years.

There's not much simplification to be done here. To dynamically generate those columns, 2016, 2018, based on the contents of the table, this approach requires two statement executions. Beware group_concat_max_len. — spencer7593
– spencer7593, Commented Nov 13, 2018 at 5:10
For performance improvement, I'd make the first query a little more complicated... instead of ... FROM results, I'd do ... FROM ( SELECT year FROM results GROUP BY year ) v rt gwt that result whittled down to distinct values of year, and then run that through the GROUP_CONCAT aggregate. An index with leading column of year should help with the GROUP BY performance. — spencer7593
– spencer7593, Commented Nov 13, 2018 at 5:16
Are the groups fixed? If so, it'd be significantly easier to make the groups columns, and the years rows. — fubar
– fubar, Commented Nov 13, 2018 at 5:16

fubar · Accepted Answer · 2018-11-14 00:33:20Z

1

You can get the results aggregated by year using the following query.

You will need to add a new column for each faculty colour, but given that this is a known finite list that shouldn't be a problem.

SELECT 
    MAX(year) AS year, 
    MAX(IF(faculty = 'Yellow', w_gender, NULL)) AS yellow,
    MAX(IF(faculty = 'Green', w_gender, NULL)) AS green,
    MAX(IF(faculty = 'Purple', w_gender, NULL)) AS purple
FROM results
GROUP BY year

Here's a simplified working DB fiddle: https://www.db-fiddle.com/f/uoX44nDLSji344iXCdmtfV/0

edited Nov 14, 2018 at 0:33

answered Nov 14, 2018 at 0:27

fubar

17.4k4 gold badges43 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I simplify MySQL join rows as columns?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related