MySQL Join Multiple Rows as Columns

Question

Say I have two tables in a MySQL Database.

Table 1:

ID    Name
1     Jim
2     Bob

Table 2:

ID    Place    Race_Number
1     2nd      1
1     3rd      2
1     4th      3
2     1st      1
2     2nd      2
2     2nd      3

When selecting rows from the database, is there any way to join rows from the second table as columns to the first table? Currently I am using SELECT * FROM Table1 NATURAL JOIN Table2.

This outputs:

ID   Name    Place    Race_Number
1    Jim     2nd      1
1    Jim     3rd      2
1    Jim     4th      3
2    Bob     1st      1
2    Bob     2nd      2
2    Bob     2nd      3

Currently I am sorting through this in my PHP script to sort it into an array. This is a pain, as I have to look at the IDs and see if they're the same and then sort accordingly. I feel like there is a way to do this right in MySQL, without having to sort it into an array in the PHP. There can be an unlimited number of entries in the second table for each ID.

The desired result right from the MySQL query is:

ID    Name    Race1    Race2    Race3
1     Jim     2nd      3rd      4th
2     Bob     1st      2nd      2nd

I can't make columns for Race1, Race2 etc in the table themselves because there can be an unlimited number of races for each ID.

Thanks for any help!

Solution

An INNER JOIN will suffice your needs. MySQL has no PIVOT function by you can still simulate it using CASE and MAX() function.

SELECT  a.ID, a.NAME,
        MAX(CASE WHEN b.Race_Number = 1 THEN b.Place ELSE NULL END) Race1,
        MAX(CASE WHEN b.Race_Number = 2 THEN b.Place ELSE NULL END) Race2,
        MAX(CASE WHEN b.Race_Number = 3 THEN b.Place ELSE NULL END) Race3
FROM    Table1 a
        INNER JOIN Table2 b
            ON a.ID = b.ID
GROUP   BY a.ID, a.Name

But if you have unknown number of RACE, then a DYNAMIC SQL is much more preferred.

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
    CONCAT('MAX(CASE WHEN b.Race_Number = ', Race_Number,
      ' THEN b.Place END) AS ', CONCAT('`Race', Race_Number, '`'))
    ) INTO @sql
FROM Table2;

SET @sql = CONCAT('SELECT s.Student_name, ', @sql, ' 
                   FROM Table1 a
                   LEFT JOIN Table2 b 
                        ON ON a.ID = b.ID
                   GROUP   BY a.ID, a.Name');

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

Comments

[CleanUp]

In i similar query (stackoverflow.com/q/24531320/1560056), I had problems with the order of the columns, which were not always in ascending order. To fix this, specify the order by adding "ORDER BY Race_Number DESC" in the end of GROUP_CONCAT(). Worked for me.

[Allan Karlson]

That's a great answer, also one can use MAX for VARCHAR columns!