MySQL select all table data, gouped by one column data

I have a table that looks like this:

serial|vehicule|alert_emails
12411|AAA|yes
12411|BBB|yes
13411|CCC|yes
13411|DDD|yes
14411|EEE|yes

I want to do a mysql query to select all data and organize it by serial field to get a array result like this:

12411
   AAA|yes
   BBB|yes
13411
   CCC|yes
   DDD|yes
14411
   EEE|yes

I tried group by the field serial but I’m not getting the desired result:

SELECT * FROM mytable GROUP BY serial;

Any help please?
Thanks.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Use UNION ALL to get the distinct serials of the table and all the rows of the table:

SELECT CASE WHEN t.col IS NULL THEN t.serial END serial, t.col
FROM (
  SELECT DISTINCT serial, null AS col
  FROM mytable
  UNION ALL
  SELECT serial, CONCAT(vehicule, '|', alert_emails)
  FROM mytable
) t  
ORDER BY t.serial, t.col IS NULL DESC

See the demo.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x