Combine multiple rows into one row MySQL
multiple-columns, mysql, rows
Solution
This type of data transformation is known as a PIVOT. MySQL doesn't have a pivot function but you can replicate it using an aggregate function with a `CASE` expression:
select t1.id,
t1.name,
max(case when t2.`key` = 'address' then t2.value end) address,
max(case when t2.`key` = 'city' then t2.value end) city,
max(case when t2.`key` = 'region' then t2.value end) region,
max(case when t2.`key` = 'country' then t2.value end) country,
max(case when t2.`key` = 'postal_code' then t2.value end) postal_code,
max(case when t2.`key` = 'phone' then t2.value end) phone
from table1 t1
left join table2 t2
on t1.id = t2.id
group by t1.id, t1.name
See SQL Fiddle with Demo.
This could also be written using multiple joins on your `table2` and you would include a filter on the join for each `key`:
select t1.id,
t1.name,
t2a.value address,
t2c.value city,
t2r.value region,
t2y.value country,
t2pc.value postal_code,
t2p.value phone
from table1 t1
left join table2 t2a
on t1.id = t2a.id
and t2a.`key` = 'address'
left join table2 t2c
on t1.id = t2c.id
and t2c.`key` = 'city'
left join table2 t2r
on t1.id = t2r.id
and t2c.`key` = 'region'
left join table2 t2y
on t1.id = t2y.id
and t2c.`key` = 'country'
left join table2 t2pc
on t1.id = t2pc.id
and t2pc.`key` = 'postal_code'
left join table2 t2p
on t1.id = t2p.id
and t2p.`key` = 'phone';
See SQL Fiddle with Demo.
The above two versions will work great if you have a limited number of `key` values. If you have an unknown number of values, then you will want to look at using a prepared statement to generate dynamic SQL:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'max(case when t2.`key` = ''',
`key`,
''' then t2.value end) AS `',
`key`, '`'
)
) INTO @sql
from Table2;
SET @sql
= CONCAT('SELECT t1.id, t1.name, ', @sql, '
from table1 t1
left join table2 t2
on t1.id = t2.id
group by t1.id, t1.name;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
See SQL Fiddle with Demo
All versions will give a result:
| ID | NAME | ADDRESS | CITY | REGION | COUNTRY | POSTAL_CODE | PHONE |
|----|------|----------|--------|--------|---------|-------------|-----------|
| 1 | Jim | X Street | NY | (null) | (null) | (null) | 123456789 |
| 2 | Bob | (null) | (null) | (null) | (null) | (null) | (null) |
| 3 | John | (null) | (null) | (null) | (null) | (null) | (null) |
Problem
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!