Combine multiple rows into one row MySQL

Question

Say I have two tables in a MySQL Database.

Table 1:

ID    Name
1     Jim
2     Bob
3     John

Table 2:

ID    key           value
1     address       "X Street"
1     city          "NY"
1     region        "NY"
1     country       "USA"
1     postal_code   ""
1     phone         "123456789"

When selecting rows from the database, is there any way to join rows from the second table as columns to the first table?

The desired result right from the MySQL query is:

ID    Name    address    city    region   country   postal_code   phone
1     Jim     X Street   NY      NY       USA       NULL          123456789
2     Bob     NULL       NULL    NULL     NULL      NULL          NULL
3     John    NULL       NULL    NULL     NULL      NULL          NULL

Thanks for any help!

Answer 1

You have a structure called entity-attribute-value in the second table. There are two ways to do the combination. I think the aggregation method is the easier to express:

select t1.name,
       max(case when `key` = 'address' then value end) as address,
       max(case when `key` = 'city' then value end) as city,
       max(case when `key` = 'region' then value end) as region,
       max(case when `key` = 'country' then value end) as country,
       max(case when `key` = 'postal_code' then value end) as postal_code,
       max(case when `key` = 'phone' then value end) as phone
from table1 t1 left join
     table2 t2
     on t1.id = t2.id
group by t1.name;

The second method is to do separate joins for each value:

select t1.name, address.value, city.value, . . .
from table1 t1 left join
     table2 address
     on t1.id = address.id and address.`key` = 'Address' left join
     table2 city
     on t1.id = city.id and city.`key` = 'City' . . .

Depending on the structure of the data, the join method can actually be faster in MySQL when it uses appropriate indexing. (Other databases have been algorithms for aggregation, so the group by method often works well in other databases.)