CODEWITHSUNDEEP
mysqlsql
Jazzkatt
Jazzkatt

Reputation: 65

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

Upvotes: 1

Views: 47

Answers (2)

Ramin Darvishov
Ramin Darvishov

Reputation: 1039

You can use group by and GROUP_CONCAT with ORDER BY and SEPERATOR

select retailer, 
GROUP_CONCAT(register ORDER BY register DESC SEPARATOR ', ') AS register, 
GROUP_CONCAT(store ORDER BY store DESC SEPARATOR ', ') AS store
from my_table 
GROUP BY retailer

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

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

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

Upvotes: 2

Related Questions