how to merge multiple rows with same id mysql

Question

I'm new posting here but the community have been my best resource on my projects so far. I'm a dumb/dummy Mysql "wanna be" and I'm in the middle of a project that is making me go mad.

I have a table from wordpress plugin buddypress that pairs meta_key and meta_values in order to create something akin to a taxonomy. My duty is to use these paired values to implement an advanced group search. Here is the original table:

--------------------------------------------
id | group_id | meta_key           | meta_value
--------------------------------------------
1  | 1        | time-zone          | Kwajalein
2  | 1        | playstyle          | hardcore
3  | 1        | recruiting-status  | Open
4  | 1        | ilvl               | 115
5  | 1        | main-raid          | Final Coil of Bahamut
6  | 1        | voicechat          | fc.teamspeak3.com

etc....

Using a view I managed to create a more friendly searchable table for begginers :

gid| time-zone| playstyle  | main-raid
--------------------------------------------
1  |          |            | 
1  |Kwajalein |            | 
1  |          | hardcore   | 
1  |          |            | 
1  |          |            | Final Coil of Bahamut
1  |          |            |

And here is the view code:

SELECT distinct
group_id AS 'gid',
IF(meta_key='recruiting-status',meta_value,'') AS 'Recruitment',
IF(meta_key='server',meta_value,'') AS 'server',
IF(meta_key='time-zone',meta_value,'') AS 'tzone',
IF(meta_key='main-raid',meta_value,'') AS 'raid',
IF(meta_key='raid-days',meta_value,'') AS 'days',
IF(meta_key='playstyle',meta_value,'') AS 'playstyle',
IF(meta_key='raid-progression',meta_value,'') AS 'progression',
IF(meta_key='raid-time',meta_value,'') AS 'time',
IF(meta_key='tanker-spot',meta_value,'') AS 'tank',
IF(meta_key='healer-spot',meta_value,'') AS 'healer',
IF(meta_key='melee-dps-spot',meta_value,'') AS 'melee',
IF(meta_key='ranged-dps-spot',meta_value,'') AS 'ranged',
IF(meta_key='magic-dps-spot',meta_value,'') AS 'magic',
IF(meta_key='ilvl',meta_value,'') AS 'ilvl',
IF(meta_key='voicechat',meta_value,'') AS 'voice',
IF(meta_key='voicechatpass',meta_value,'') AS 'voicep',
FROM wpstatic_bp_groups_groupmeta

The point is, I need to merge that result (view) so all the group_id=1 or 2 or 3, etc stand in one single row, like this:

gid| time-zone| playstyle  | main-raid
--------------------------------------------
1  |Kwajalein | hardcore   | Final Coil of Bahamut
2  |SaoPaulo  | regular    | Second Coil of Bahamut

etc

Can anyone help me there?

This is similar to this http://stackoverflow.com/questions/16568228/how-to-transpose-mysql-table-rows-into-columns but I think you can add to the end of your view `GROUP BY gid` maybe? — Dave Goten, Feb 05 '15 at 20:21

shmosel · Accepted Answer · 2015-02-05T21:45:54.910

2

Just surround your IFs in a MAX, or another aggregate function that will capture the non-empty strings (e.g., GROUP_CONCAT), and add a GROUP BY group_id add the end. For example,

SELECT
group_id AS gid,
MAX(IF(meta_key='recruiting-status',meta_value,'')) AS 'Recruitment',
MAX(IF(meta_key='server',meta_value,'')) AS 'server',
...
FROM wpstatic_bp_groups_groupmeta
GROUP BY group_id

edited Feb 05 '15 at 21:45

answered Feb 05 '15 at 21:30

shmosel

49,289
6
73
138

Thanks, i'll try it right away, I have tried the group by id but not the max. :) – Cristiane Cruz Feb 06 '15 at 11:02

how to merge multiple rows with same id mysql

1 Answers1