Join MySQL tables with key value pairs

Question

I have a table structure as described below:

persons
+----+------+
| id | name |
+----+------+
| 1  | Bart |
| 2  | Lisa |
+----+------+

keys
+----+--------+
| id | key    |
+----+--------+
| 1  | gender |
| 2  | age    |
+----+--------+

values
+----+-----------+--------+--------+
| id | person_id | key_id | value  |
+----+-----------+--------+--------+
| 1  | 1         | 1      | male   |
| 2  | 1         | 2      | 10     |
| 3  | 2         | 1      | female |
| 4  | 2         | 2      | 8      |
+----+-----------+--------+--------+

And I will need to get the result of the table as follows:

+-----------+------+--------+-----+
| person_id | name | gender | age |
+-----------+------+--------+-----+
| 1         | Bart | male   | 10  |
| 2         | Lisa | female | 8   |
+-----------+------+--------+-----+

I can achieve this using LEFT JOINs, but this does not work dynamically.

I could create a PHP script that will generate SQL, but there should be a way to make a query that works dynamically.

Answer 1

Conditional Aggregation + Join

SELECT p.person_id,
       p.NAME,
       Max(CASE WHEN key_id = 1 THEN value END) AS Gender,
       Max(CASE WHEN key_id = 2 THEN value END) AS Age
FROM   VALUES v
       JOIN person p
         ON v.person_id = p.id
GROUP  BY p.person_id,
          p.NAME 

Get rid of the table Keysand Valuesand add two columns with the name Ageand Genderto the Persontable