Jazzkatt Jazzkatt - 2 months ago 8
MySQL Question

MySql query two fields together in results

So I have the following table:

retailer register store
1 104 b
1 101 a
2 104 b
2 101 a
3 104 b
3 101 a


I am looking to query the table to get a result that looks like:

retailer register store
1 _ 101, 104 _ a, b
2 _ 101, 104 _ a, b
3 _ 101, 104 _ a, b

Answer

You can use group_concat and group by

 select 
    retailer
 , group_concat(register ORDER BY registed ASC SEPARATOR ', ')
 , group_concat(store ORDER BY store ASC SEPARATOR ', ')
 from my_table 
 group by retailer 

and if you need the saparator you should use a concat

 select 
      concat ( retailer
      , '_'
      ,  group_concat(register ORDER BY registed ASC SEPARATOR ', ')
      , '_'
      , group_concat(store ORDER BY store ASC SEPARATOR ', '))
 from my_table 
 group by retailer 
Comments