MySQL pivot in same table

Question

MySQL pivot in same table with dynamic content

Create Table Code

CREATE TABLE `product_table` (
    `id` INT(10) NOT NULL,
    `pdate` DATE NULL DEFAULT NULL,
    `product` VARCHAR(50) NULL DEFAULT NULL,
    `counts` VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

I have table structure as below

+----+------------+---------+--------+
| id |   pdate    | product | counts |
+----+------------+---------+--------+
|  1 | 2015-10-12 | BOX     |     74 |
|  2 | 2015-10-12 | SHOE    |     35 |
|  3 | 2015-10-12 | PEN     |     38 |
|  4 | 2015-10-12 | WATCH   |     36 |
|  5 | 2015-10-13 | BOX     |     36 |
|  6 | 2015-10-13 | SHOE    |     80 |
|  7 | 2015-10-13 | PEN     |     70 |
|  8 | 2015-10-13 | WATCH   |     73 |
+----+------------+---------+--------+

I would like to have report as this format

+---------+------------+------------+
| product | 2015-10-12 | 2015-10-13 |
+---------+------------+------------+
| BOX     |         74 |         36 |
| SHOE    |         35 |         80 |
| PEN     |         38 |         70 |
| WATCH   |         36 |         73 |
+---------+------------+------------+

what i tried so far

select 
    d.p product,
    (select date(p.pdate) from product_table p where date(p.pdate)=d.dt and p.product = d.p ) date,
    (select p.counts from product_table p where date(p.pdate)=d.dt and p.product = d.p ) cnt
from
(select pt.product p,date(pt.pdate) dt from product_table pt group by pt.product,date(pt.pdate) ) as d
group by product

are the dates argument or will you get them from the table

Bernd Buffen
– Bernd Buffen

2015-10-13 11:42:42 +00:00
Commented Oct 13, 2015 at 11:42 — Bernd Buffen
– Bernd Buffen, Commented Oct 13, 2015 at 11:42
@BerndBuffen will get from table

vickisys
– vickisys

2015-10-13 11:43:42 +00:00
Commented Oct 13, 2015 at 11:43 — vickisys
– vickisys, Commented Oct 13, 2015 at 11:43
so it can many field, for each date 1 ?

Bernd Buffen
– Bernd Buffen

2015-10-13 11:44:37 +00:00
Commented Oct 13, 2015 at 11:44 — Bernd Buffen
– Bernd Buffen, Commented Oct 13, 2015 at 11:44
@BerndBuffen couldn't get you

vickisys
– vickisys

2015-10-13 11:45:43 +00:00
Commented Oct 13, 2015 at 11:45 — vickisys
– vickisys, Commented Oct 13, 2015 at 11:45
Google: "mysql dynamic pivot".

Gordon Linoff
– Gordon Linoff

2015-10-13 12:11:08 +00:00
Commented Oct 13, 2015 at 12:11 — Gordon Linoff
– Gordon Linoff, Commented Oct 13, 2015 at 12:11

Jerko W. Tisler · Accepted Answer · 2015-10-13 12:49:52Z

1

Unfortunately MySQL does not have implemented table pivoting. So there is workaround with building a dynamic query, here is my example:

SELECT 
GROUP_CONCAT(DISTINCT(
     CONCAT(
        'MAX(
            IF(pt.pdate =  \'', pdate, '\', pt.counts, null)
         ) AS \'' , pdate, '\''
     )
   )
) INTO @dates FROM product_table;

SET @query = CONCAT('SELECT product, ', @dates, ' FROM product_table pt   GROUP BY product');

PREPARE stmt FROM @query;
EXECUTE stmt;

Please note that if you have a lot of dates in your table it may be very slow

answered Oct 13, 2015 at 12:49

Jerko W. Tisler

98910 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

vickisys Over a year ago

it works flawless, is it possible to excecute using jstl and jsp

Jerko W. Tisler Over a year ago

Unfortunately I've never worked with jstl nor jsp, so I can't answer on your question. BTW you marked wrong answer as correct :)

Bernd Buffen · Accepted Answer · 2015-10-13 13:08:40Z

0

please try this: remove the remark to see the Query

 SELECT DISTINCT
  CONCAT(   'SELECT p.product  ',
  GROUP_CONCAT( 
  CONCAT(   ',SUM( IF(pdate = \'', pdate,'\', p.count,0)) as \'', pdate,'\'') SEPARATOR '\n'),
  ' FROM product_table p GROUP BY p.product;') INTO @sql FROM product_table p;

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

edited Oct 13, 2015 at 13:08

answered Oct 13, 2015 at 12:16

Bernd Buffen

15.1k2 gold badges27 silver badges41 bronze badges

Collectives™ on Stack Overflow

MySQL pivot in same table

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related