In MySQL, how do I get the value of each entry for each day?

Question

So, I have a MySQL table that is similar this one:

|Day | Participant| Score |
+----+------------+-------+
|Mon | Andy       | 9     |
|Mon | Betty      | 8     |
|Mon | Charlie    | 7     |
|Tue | Andy       | 6     |
|Tue | Betty      | 6     |
|Tue | Charlie    | 8     |
|Wed | Andy       | 7     |
|Wed | Charlie    | 4     |

I want to output it into this:

| Day | Andy | Betty | Charlie |
+-----+------+-------+---------+
| Mon | 9    | 8     | 7       |
| Tue | 6    | 6     | 7       |
| Wed | 7    | null  | 4       |

I approached this problem with PHP and this is what I came up with:

Query the names of each participant.
With PHP, build a sub-query for each participant's score in each day, like this one:
```
SELECT score FROM TableName WHERE (Day=ref_point AND Participant='Andy')
```

Generate the main query, with all the sub-queries included, like this:

SELECT Day AS ref_point,(/* Sub-query for Andy */) AS Andy,(/* Sub-query for Betty */) AS Betty,(/*Sub-queries for the rest of participants */) AS Others FROM TableName GROUP BY Day

This works quite fine, but as the number of participants grow, so does the query string. I fear that one day the query become longer than PHP's max string length. I need to know how to do this entirely in MySQL.

StackOverflow is not the proper place for this question. We do not write your code for you. You need to do your own coding and if you aren't sure why something is not working as expected, post the code with an explanation of what you were expecting it to do, and what it is actually doing including all error messages. See [about StackOverflow](http://stackoverflow.com/about). — John Conde, Mar 22 '13 at 13:10
Lots of good posts on here to get you started about transposing data (Rows to Columns or Columns to Rows) [other posts about this](http://stackoverflow.com/questions/1241178/mysql-rows-to-columns) — mconlin, Mar 22 '13 at 13:12

score 3 · Accepted Answer · answered Mar 22 '13 at 13:11

SELECT  DAY,
        MAX(CASE WHEN Participant = 'Andy' THEN Score END) `Andy`,
        MAX(CASE WHEN Participant = 'Betty' THEN Score END) `Betty`,
        MAX(CASE WHEN Participant = 'Charlie' THEN Score END) `Charlie`
FROM    tableName
GROUP   BY DAY

SQLFiddle Demo

If you have unknown number of participant, a much better way is to use Dynamic SQL, eg

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(case when Participant = ''',
      Participant,
      ''' then Score end) AS ',
      Participant
    )
  ) INTO @sql
FROM TableName;

SET @sql = CONCAT('SELECT  DAY, ', @sql, ' 
                  FROM    tableName
                  GROUP   BY DAY');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

OUTPUT

╔═════╦══════╦════════╦═════════╗
║ DAY ║ ANDY ║ BETTY  ║ CHARLIE ║
╠═════╬══════╬════════╬═════════╣
║ Mon ║    9 ║ 8      ║       7 ║
║ Tue ║    6 ║ 6      ║       8 ║
║ Wed ║    7 ║ (null) ║       4 ║
╚═════╩══════╩════════╩═════════╝

In MySQL, how do I get the value of each entry for each day?

1 Answers1