CODEWITHSUNDEEP
mysqlsql
Peter
Peter

Reputation: 3184

Simple MySQL Query - Change table format around

I'm fairly sure this is a fairly easy answer but the answer is completely slipping my mind.

I have a database table that is currently formatted like:

event_id | elem_id | value
   1          1      Value 1
   1          2      Value 2
   2          1      Value 3
   2          2      Value 4

Both event_id and elem_id are undetermined numbers and have infinite possibilities.

How would I query it for example based on event_id 1 to get the data to be formatted as such:

event_id |    1    |     2
   1       Value 1    Value 2

Knowing that elem_id is a number >= n so potentially there could be 50 elem_id yet I still need the data in that format.

Like I said I can't for the life of me figure out the query to assemble it that way. Any help would be GREATLY appreciated.

Upvotes: 1

Views: 103

Answers (1)

Electronick
Electronick

Reputation: 1122

Try following:

SELECT 
    `event_id`, 
    (SELECT t2.`value` FROM table t2 WHERE t2.`event_id` = t1.`event_id` AND t2.`elem_id` = 1), 
    (SELECT t3.`value` FROM table t3 WHERE t3.`event_id` = t1.`event_id` AND t3.`elem_id` = 2) 
FROM `table` t1 GROUP BY `event_id`;

Also you can use different way, and get elem_ids and values in comma-separated format in two cells

SELECT `event_id`, GROUP_CONCAT(`elem_id`), GROUP_CONCAT(`value`) FROM `table` GROUP BY `event_id`;

and you can change separator with following syntax: GROUP_CONCAT(field SEPARATOR '::')

Upvotes: 1

Related Questions