How do I pivot MySQL INNER JOIN results in PHP?

Question

I have the following 2 MySQL tables:

players:

| id | name    |
|----|---------|
| 1  | Player1 |
| 2  | Player2 |
| 3  | Player3 |

scores:

| key | id | round | score |
|-----|----|-------|-------|
| 1   | 1  | Rd1   | 20    |
| 2   | 1  | Rd2   | 22    |
| 3   | 1  | Rd3   | 19    |
| 4   | 2  | Rd1   | 18    |
| 5   | 2  | Rd2   | 23    |
| 6   | 2  | Rd3   | 19    |

where scores.id=players.id

I will have upwards of 90 players in my 'players' table, what's the best way to query this and insert it into an HTML table to make it easier to view? I'm hoping to have an output similar to this:

| Player  | Round 1 | Round 2 | Round 3 |
|---------|---------|---------|---------|
| Player1 | 20      | 22      | 19      |
| Player2 | 18      | 23      | 19      |

This is my first attempt at normalizing data in tables. Am I going to have to do number of cases? I'm not sure what the best way to pivot the data is with an INNER JOIN.

Answer 1

This is my solution, hope it helps :

SELECT
    name as Player,  
    SUM(CASE WHEN (s.round='Rd1') THEN s.score ELSE 0 END) AS Round1,
    SUM(CASE WHEN (s.round='Rd2') THEN s.score ELSE 0 END) AS Round2,
    SUM(CASE WHEN (s.round='Rd3') THEN s.score ELSE 0 END) AS Round3

FROM 
    players p
    JOIN scores s
    on s.id=p.id
GROUP BY 
    name

This will output :

| Player  |  Round1 |  Round2 |  Round3 |
|---------|---------|---------|---------|
| Player1 | 20      | 22      | 19      |
| Player2 | 18      | 23      | 19      |

This Fiddle for you to test!