使用键值对连接MySQL表

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 would need to get result a table like this:

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

I can achieve this by using LEFT JOINs, but that doesn't work dynamically.

I could make a PHP script that would generate the SQL, but there must be 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 Keys and Values table and add two columns called Age and Gender in Person table itself