mysql建立数据透视表,MySQL数据透视表

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

解决方案

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