Query PIVOT or SUM

Question

My consultation is as follows::

 mysql> select product_id, presentation, price from Variant where product_id = "1604";
  +------------+-------------------------+-------+
  | product_id | presentation            | price |
  +------------+-------------------------+-------+
  |       1604 | Unitário = R$ 11,90     |  11.9 |
  |       1604 | 5 Peças = R$ 5,00 cada  |    25 |
  |       1604 | Bluesky Todas           |    15 |
  +------------+-------------------------+-------+
   3 rows in set (0,00 sec)

How would I separate these 3 results for the same 1604 ID into separate columns in SQL?

Example: presentation1, presentation2, presentation3 column-separated

Answer 1

Simulate the Pivot dynamically

Mysql does not have Pivot. In that case you have two options.

• Generate a column for each case in one SUM with CASE.
• Dynamically generate the same SQL if the column content is dynamic.

In the static case, it would be something like:

select product_id,
  sum(case when presentation = 'Unitário = R$ 11,90 ' THEN price END) presentation1,
  sum(case when presentation = '5 Peças = R$ 5,00 cada' THEN price END) presentation2,
  sum(case when presentation = 'Bluesky Todas' THEN price END) presentation3
from product
where product_id = 1604

In the dynamic case, it would look something like:

SET @sql = NULL;
SET @rowid = 0;

SELECT
    GROUP_CONCAT(
    CONCAT(
    'sum(case when presentation = ''',
      presentation,
      ''' then price end) AS ',
      'presentation',
      @rowid := @rowid + 1 
    )
  ) INTO @sql 
FROM 
(SELECT presentation 
 FROM product
 GROUP BY presentation
) AS p;

SET @sql = CONCAT(
  'SELECT product_id, ', 
  @sql, 
  ' from product where product_id = 1604'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

I added the dynamic query to this Fiddle to see working with more records.