Query PIVOT or SUM

Question

Query PIVOT or SUM

Navigation

#1 by (1 votes)

0

My query 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 do to separate these 3 results for the same ID 1604 into separate columns in SQL?

Example: presentation1, presentation2, presentation3 separated in columns

mysql sql

asked by anonymous 13.12.2017 / 14:36

1 answer

Feedback to the user You are bringing the entire column and not just a specific id

score 1 · Answer 1

Simulating the Pivot Dynamically

MySQL does not have Pivot functionality. In this case you have two options.

Generate a column for each case in SUM with CASE .
Dynamically generate this same SQL if the column contents are dynamic.

In the static case, it would look 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 in this Fiddle to see it working with more records.