Pivot table with multiple identical column names

[Driesdk]

Hi,

I want to pivot a table and at this moment i use:

SELECT e.guid AS id, e.guid AS guid,
TYPE , subtype, md.owner_guid AS owner_guid, site_guid, container_guid, md.access_id AS access_id, username, name, e.time_created, time_updated, name, e.enabled, banned, e.last_action, last_login, MAX( IF( msn.string = 'custom_profile_type', msv.string, NULL ) ) AS custom_profile_type, MAX( IF( msn.string = 'field1', msv.string, NULL ) ) AS field1,
MAX( IF( msn.string = 'field1', msv.string, NULL ) ) AS field1
FROM exp_metadata md
JOIN exp_metastrings msn ON md.name_id = msn.id
JOIN exp_metastrings msv ON md.value_id = msv.id
JOIN exp_users_entity u ON u.guid = md.entity_guid
JOIN exp_entities e ON e.guid = md.entity_guid
GROUP BY guid

You can see that I need the field 'field1' as a column with the value attached to it.

The problem is that there are multiple fields called 'field1' basicly the options of a multiselect that my users can choose.
Obviously using MAX I get the maximum value, ex. when they selected answer1, answer2 and answer3, this query will only fetch answer3.

 

I want to know if there is a way that instead of just 'answer3' I can have the value to be: answer1,answer2,answer3

 

I'm sorry if it is not really clear.

Thanks in advance.

Edited March 6, 2014 by Driesdk