Merging columns from ORDER BY and keeping sperate with a hypen and only showing the field used to OR-CodePudding

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.

CodePudding user response：

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