-1

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.

Panda
  • 6,955
  • 6
  • 40
  • 55
  • 1
    Possible duplicate of [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table) – Madhur Bhaiya Nov 13 '18 at 05:10
  • 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 Nov 13 '18 at 05: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 Nov 13 '18 at 05:16
  • Are the groups fixed? If so, it'd be significantly easier to make the groups columns, and the years rows. – fubar Nov 13 '18 at 05:16
  • @spencer7593 Thanks for the suggestions, I'll try that out. – Panda Nov 13 '18 at 05:17
  • Group (yellow, green, purple), not gender. – fubar Nov 13 '18 at 05:17
  • @fubar Yep, group is fixed, but there are 15 colours in total. – Panda Nov 13 '18 at 05:17
  • So do you specifically need the years as columns, or could you flip the result table? – fubar Nov 13 '18 at 05:18
  • @fubar I'm fine with flipping it – Panda Nov 13 '18 at 05:19
  • This is not clear. What are "join rows as columns" & "provide an overview of the gender of the class president over different years" supposed to mean? "Essentially" when not introducing or summarizing a clear detailed presentation means "unclearly". Please use enough words, sentences & references to parts of examples to clearly say what you mean. Please read & act on [mcve]. Please clarify via post edits, not comments. Please do not append UPDATEs/EDITs, edit your post to be the best presentation. Please format code reasonably. PS Your UPDATE is not clear either. Use enough words & sentences. – philipxy Nov 13 '18 at 05:37
  • This is going to be a faq. Don't just use one post you found. There are a zillion including better answers not restricted by GROUP_CONCAT. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on [mcve]. – philipxy Nov 13 '18 at 05:45
  • @Panda, sorry I forgot about this yesterday. I've just posted an answer. – fubar Nov 14 '18 at 00:27

1 Answers1

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

fubar
  • 16,918
  • 4
  • 37
  • 43