Home Merging columns from ORDER BY and keeping sperate with a hypen and only showing the field used to ORDER BY once at the end of each group

Questions

Merging columns from ORDER BY and keeping sperate with a hypen and only showing the field used to ORDER BY once at the end of each group

byMR

February 21, 2022

I have a MySQL table like the following:

BurgerExtra ID	Burger ExtraName	BurgerExtraPrice
1	Mayo	1
2	Burger Sauce	1
3	Ketchup	1
4	Hot Chilli Sauce	1
5	Pickles	1
6	Musard	1
7	Lettuce	1
8	Tomatoes	1
9	Grilled Onions	1
10	Onions	1
11	Jalapeños	1
12	American Cheese	1
13	Red Relish	1
14	Chipotle	1
15	Bacon	4
16	Egg	2
17	Cheese Sauce	3

But I need to show the data in the following format:

Mayo – Burger Sauce – Ketchup – Hot Chilli Sauce – Pickles – Mustard – Lettuce
Tomatoes – Grilled Onions – Onions – Jalapeños – American Cheese – Red Relish
Chipotle Mayo 1 Bacon 4 – Egg 2 – Cheese Sauce 3

I have tried

SELECT BurgerExtraName, BurgerExtraPrice FROM `BurgerExtras` ORDER BY BurgerExtraPrice;

and

SELECT BurgerExtraName, BurgerExtraPrice FROM `BurgerExtras` GROUP BY BurgerExtraPrice;

but neither seem to on the right path to do what I am trying to achieve. I should mention that I am working in PHP but I want to find out if it possible to do this in MySQL first.

>Solution :

You can use group_concat:

select group_concat(
  concat(BurgerExtraName, 
         case when BurgerExtraPrice > 1 then concat(' ', BurgerExtraPrice) else '' end)
  order by BurgerExtraID 
  separator ' - '
)
from BurgerExtras

Fiddle