Select corresponding column based on columns

In MySQL, I try to select a corresponding value based on the greatest value between columns.

Let’s take as example table ‘prices’:

id	supplier	price1	quantity1
1	Microsoft	8,27	1000
1	Apple	10,25	2000
1	IBM	12,25	3000
2	Microsoft	9,45	2000
2	Apple	7,55	4000
2	IBM	6,78	6000

The result I intend to get is:

id	price_microsoft	price_apple	price_ibm	best price	best price corresponding quantity
1	8,27	10,25	12,25	8,27	1000
2	9,45	7,55	6,78	6,78	4000

Is there a possibillity to avoid a self join? Or is the self join just the way to do it?

Answer

If you are using MySQL 8

select distinct
 id, 
 sum(case when supplier = 'Microsoft' then price1 else 0 end) over by_id price_ms,
 sum(case when supplier = 'Apple' then price1 else 0 end) over by_id price_apple,
 sum(case when supplier = 'IBM' then price1 else 0 end) over by_id price_ibm,
 min(price1) over by_id best_price,
 first_value(quantity1) over by_id_price best_price_qty
from prices
window 
by_id as (partition by id),
by_id_price as (partition by id order by price1 asc)

Demo