Mysql里的Pivoting

“这里说的是静态pivoting查询，即用户需要提前知道旋转的属性列的值。对于动态pivoting，需要动态地构造查询字符串。”

“pivoting是一项可以将行旋转为列的技法，在执行pivoting时可能会用到聚合。”

先说一下开放架构，它是一种用于频繁更改架构 的一种设计模式。比如在某个时候要对表结构进行修改，用户必须添加、修改或删除列。

有这样一个例子：

mysql> select * from t_pivoting;
+----+-----------+------------+
| id | attribute | value      |
+----+-----------+------------+
|  1 | attr1     | BMW        |
|  1 | attr2     | 100        |
|  1 | attr3     | 2010-01-01 |
|  2 | attr2     | 200        |
|  2 | attr3     | 2010-03-04 |
|  2 | attr4     | M          |
|  2 | attr5     | 55.60      |
|  3 | attr1     | SUV        |
|  3 | attr2     | 10         |
|  3 | attr3     | 2011-11-11 |
+----+-----------+------------+
10 rows in set

在表中有这样一列：attribute，它的类型是VARCHAR,它可容纳各种类型的数据。而它的值可用来存储将来要增加的列（明明添加的是行记录，后来会变成列名）。

转换的SQL语句为：

mysql> select id,

max(case when attribute='attr1' then value end) as attr1,

max(case when attribute='attr2' then value end) as attr2,

max(case when attribute='attr3' then value end) as attr3,

max(case when attribute='attr4' then value end) as attr4,

max(case when attribute='attr5' then value end) as attr5 

from t_pivoting 

group by id;
+----+-------+-------+------------+-------+-------+
| id | attr1 | attr2 | attr3      | attr4 | attr5 |
+----+-------+-------+------------+-------+-------+
|  1 | BMW   | 100   | 2010-01-01 | NULL  | NULL  |
|  2 | NULL  | 200   | 2010-03-04 | M     | 55.60 |
|  3 | SUV   | 10    | 2011-11-11 | NULL  | NULL  |
+----+-------+-------+------------+-------+-------+
3 rows in set

查询后attribute里的值都转化成列名了。这里再添加一行记录：

mysql> insert into t_pivoting select 3,'attr6','haha';
Query OK, 1 row affected
Records: 1  Duplicates: 0  Warnings: 0

再进行查询：

mysql> select id,

max(case when attribute='attr1' then value end) as attr1,

max(case when attribute='attr2' then value end) as attr2,

max(case when attribute='attr3' then value end) as attr3,

max(case when attribute='attr4' then value end) as attr4,

max(case when attribute='attr5' then value end) as attr5 ,

max(case when attribute = 'attr6' then value end) as attr6 

from t_pivoting 

group by id;
+----+-------+-------+------------+-------+-------+-------+
| id | attr1 | attr2 | attr3      | attr4 | attr5 | attr6 |
+----+-------+-------+------------+-------+-------+-------+
|  1 | BMW   | 100   | 2010-01-01 | NULL  | NULL  | NULL  |
|  2 | NULL  | 200   | 2010-03-04 | M     | 55.60 | NULL  |
|  3 | SUV   | 10    | 2011-11-11 | NULL  | NULL  | haha  |
+----+-------+-------+------------+-------+-------+-------+
3 rows in set

上面行列互转的SQL语句是这样的：

"先根据 id 进行分组，确定行列互转后记录的行数，之后通过已知的属性列来确定行列互转后的对应 的列，并通过CASE 得到每列的值。由于使用了分组技法，因此一定要使用分组函数来取得列的值，可有MAX,MIN...."