How to create pivot table using sql in mysql? (mysql pivot table)?

Question

MySQL

Hello!
MySQL
For example, we have a table:
[product_id | price_id | price] -> (Table schema)
(Data)
product1 | price1 | 100
item1 | price2 | 50
item1 | price3 | 130
product2 | price1 | 300
product2 | price2 | 70
product2 | price3 | 100
As soon as using sql, display the following result :
[item_id | price_id1 | price_id2 | price_id3 ] -> schema
data
product1 | 100 | 50 | 130
product2 | 300 | 70 | 100
That is, it turns out the vertical arrangement of the data must be transferred to the horizontal (this is how I imagine it).
Since as far as it was possible to find out in MsSQL, there is already a built-in feature for this pivot table.

Answer 1

You can do this, respectively, for each column you need to add a separate IF

select item, 
SUM(IF(price_id = 'price1', price, 0))  as price1, 
SUM(IF(price_id = 'price2', price, 0)) as price2 
FROM pivot_sample GROUP BY item;

sqlfiddle.com/#!2/d2367/1/0

Answer 2

another option:

select tovar_id,
   group_concat(price_id order by price_id asс separator ',') as price_ids,
   group_concat(price order by price_id asс separator ',') as prices
from prices group by tovar_id

And disassemble already in the application