sql - how to merge different rows into one without group_concat

Question

in this example, I have different values about same user. so I have to get all those values in single row.

user_id     key           value
-------     --------      -----------
123         name       thomps
123         url        thomps.com
123         email      [email protected]

456         name       hond
456         url        hond.com
456         email      [email protected]

how to get a list merging rows like this:

user_id    name       url           email
-------    ----       ----          ------
123        thomps     thomps.com    [email protected]
456        hond       hond.com      [email protected]

I tried using grop_concat and join sub-query but no success

You need [PIVOT](http://stackoverflow.com/questions/7674786/mysql-pivot-table) to turn your rows into cols. — user2989408, Feb 03 '14 at 17:07
A side note: You're storing your data in what's known as the Entity-Attribute-Value anti-pattern. It's generally frowned upon. see : http://dba.stackexchange.com/questions/20759/is-there-a-name-for-this-database-structure — Aheho, Feb 03 '14 at 17:14

score 5 · Accepted Answer · answered Feb 03 '14 at 17:06

Group by the user and then use a case to get the results in different columns

 select user_id, 
       max(case when `key` = 'name' then value end) as name,
       max(case when `key` = 'url' then value end) as url,
       max(case when `key` = 'email' then value end) as email
from your_table
group by user_id

sql - how to merge different rows into one without group_concat

1 Answers1