动态数据透视表pivot table(行转换列)

原文地址：http://buysql.com/mysql/14-how-to-automate-pivot-tables.html

MYSQL下如何动态生成标题的数据透视表。

先用以下script生成一张包含若干属性的表叫"properties"。

CREATE TABLE properties (
  id INT(11) NOT NULL AUTO_INCREMENT,
  item_id INT(11) DEFAULT NULL,
  property_name VARCHAR(255) DEFAULT NULL,
  value VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (id)
);
INSERT INTO properties VALUES 
  (1, 1, 'color', 'blue'),
  (2, 1, 'size', 'large'),
  (3, 1, 'weight', 65),
  (4, 2, 'color', 'orange'),
  (5, 2, 'weight', 57),
  (6, 2, 'size', 'large'),
  (7, 3, 'size', 'small'),
  (8, 3, 'color', 'red'),
  (9, 3, 'weight', 12),
  (10, 4, 'color', 'violet'),
  (11, 4, 'size', 'medium'),
  (12, 4, 'weight', 34),
  (13, 5, 'color', 'green'),
  (14, 5, 'weight', 10);

然后我们想根据原始数据生成如下report。

+----+---------+---------------+--------+ | id | item_id | property_name | value | +----+---------+---------------+--------+ | 1 | 1 | color | blue | | 2 | 1 | size | large | | 3 | 1 | weight | 65 | | 4 | 2 | color | orange | | 5 | 2 | weight | 57 | | 6 | 2 | size | large | | 7 | 3 | size | small | | 8 | 3 | color | red | | 9 | 3 | weight | 12 | | 10 | 4 | color | violet | | 11 | 4 | size | medium | | 12 | 4 | weight | 34 | | 13 | 5 | color | green | | 14 | 5 | weight | 10 | +----+---------+---------------+--------+

=>

+---------+--------+--------+--------+ | item_id | color | size | weight | +---------+--------+--------+--------+ | 1 | blue | large | 65 | | 2 | orange | large | 57 | | 3 | red | small | 12 | | 4 | violet | medium | 34 | | 5 | green | NULL | 10 | +---------+--------+--------+--------+

众所周知，MySql里并没有自动表转换的功能。当然，我们可以用一些额外的程序或工具来连接MySQL去执行数据转换。但在这里，我们来探讨下如何手动的写一个查询来实现数据转换。该查询可以这样实现：

SELECT
  item_id,
  MAX(IF(property_name = 'color', value, NULL)) AS color,
  MAX(IF(property_name = 'size', value, NULL)) AS size,
  ...
  ...
  ...
FROM
  properties
GROUP BY
  item_id;

很明显，对于任何一个'property_name'我们都做了定义，如'color', 'size'。如果属性的类型不变的话，这个查询已经没有问题了。但是如果字段'property_name'经常变，并且可能会增加一个新的，那要怎么处理呢？难道只能每次去改这个查询语句么？这种情况下，就可以使用动态生成查询语句——读取表中字段'property_name'的所有值，根据该值动态的创建一个查询语句。

动态生成查询的实现如下：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(property_name = ''',
      property_name,
      ''', value, NULL)) AS ',
      property_name
    )
  ) INTO @sql
FROM properties;
SET @sql = CONCAT('SELECT item_id, ', @sql, ' FROM properties GROUP BY item_id');

执行该语句，会生成如下的查询语句（为了阅读方便，调整了下格式）：

SELECT
  item_id,
  MAX(IF(property_name = 'color', value, NULL)) AS color,
  MAX(IF(property_name = 'size', value, NULL)) AS size,
  MAX(IF(property_name = 'weight', value, NULL)) AS weight
FROM
  properties
GROUP BY
  item_id

注意：

MySQL对于GROUP_CONCAT结果用系统变量group_concat_max_len做了限制，默认值是1024。所以，如果你的表有很多列，最好将该值设大一些。

SET @@group_concat_max_len = 5000;
SELECT GROUP_CONCAT(column_name) FROM table;

经过执行动态生成语句，查询语句被写到变量@sql里，现在我们可以用prepared statment来执行该语句：

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

结果：

+---------+--------+--------+--------+
| item_id | color  | size   | weight |
+---------+--------+--------+--------+
|       1 | blue   | large  | 65     |
|       2 | orange | large  | 57     |
|       3 | red    | small  | 12     |
|       4 | violet | medium | 34     |
|       5 | green  | NULL   | 10     |
+---------+--------+--------+--------+