MySql query two fields together in results

Question

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

Are your commas in the expected result meant to represent a column separator, or is it literally a string of all the results comma-separated? — Siyual
– Siyual, Commented Sep 29, 2016 at 19:52

ScaisEdge · Accepted Answer · 2016-09-29 20:18:36Z

2

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

edited Sep 29, 2016 at 20:18

answered Sep 29, 2016 at 19:53

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Ramin Darvishov Over a year ago

you result will be different

ScaisEdge Over a year ago

@RaminDarvishov . explain better

Ramin Darvishov Over a year ago

your result will be different order and without seperator

Ramin Darvishov Over a year ago

',' (comma) is default seperator but user want comma with space

ScaisEdge Over a year ago

@RaminDarvishov if this is what you need the asnwer is updated and also there is a '_' between the column .. concatenated

|

Ramin Darvishov · Accepted Answer · 2016-09-29 20:20:44Z

1

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

edited Sep 29, 2016 at 20:20

answered Sep 29, 2016 at 20:12

Ramin Darvishov

1,0392 gold badges15 silver badges30 bronze badges

Collectives™ on Stack Overflow

MySql query two fields together in results

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related