我将对解决这个问题的步骤进行更长、更详细的解释。如果时间太长，我很抱歉。
我将从你给出的基础开始，用它来定义几个术语，我将在这篇文章的其余部分使用这些术语。这将是基表:
select * from history;
+--------+----------+-----------+
| hostid | itemname | itemvalue |
+--------+----------+-----------+
| 1 | A | 10 |
| 1 | B | 3 |
| 2 | A | 9 |
| 2 | C | 40 |
+--------+----------+-----------+
这就是我们的目标，漂亮的数据透视表
select * from history_itemvalue_pivot;
+--------+------+------+------+
| hostid | A | B | C |
+--------+------+------+------+
| 1 | 10 | 3 | 0 |
| 2 | 9 | 0 | 40 |
+--------+------+------+------+
历史中的价值观。Hostid列将变成数据透视表中的y值。历史中的价值观。Itemname列将变成x值(原因很明显)。
当我必须解决创建数据透视表的问题时，我使用了一个三步过程(可选的第四步)来解决它:
选择感兴趣的列，即y值和x值
用额外的列扩展基表——每个x值对应一列
对扩展表进行分组和聚合——每个y值对应一组
(可选)修饰聚合表
让我们把这些步骤应用到你的问题中，看看会得到什么:
步骤1:选择感兴趣的列。在期望的结果中，hostid提供y值，itemname提供x值。
步骤2:用额外的列扩展基本表。每个x值通常需要一列。回想一下，我们的x值列是itemname:
create view history_extended as (
select
history.*,
case when itemname = "A" then itemvalue end as A,
case when itemname = "B" then itemvalue end as B,
case when itemname = "C" then itemvalue end as C
from history
);
select * from history_extended;
+--------+----------+-----------+------+------+------+
| hostid | itemname | itemvalue | A | B | C |
+--------+----------+-----------+------+------+------+
| 1 | A | 10 | 10 | NULL | NULL |
| 1 | B | 3 | NULL | 3 | NULL |
| 2 | A | 9 | 9 | NULL | NULL |
| 2 | C | 40 | NULL | NULL | 40 |
+--------+----------+-----------+------+------+------+
注意，我们没有改变行数——我们只是增加了额外的列。还要注意null的模式——itemname = " a "的行对新列a具有非空值，对其他新列具有空值。
步骤3:对扩展表进行分组和聚合。我们需要根据hostid进行分组，因为它提供了y值:
create view history_itemvalue_pivot as (
select
hostid,
sum(A) as A,
sum(B) as B,
sum(C) as C
from history_extended
group by hostid
);
select * from history_itemvalue_pivot;
+--------+------+------+------+
| hostid | A | B | C |
+--------+------+------+------+
| 1 | 10 | 3 | NULL |
| 2 | 9 | NULL | 40 |
+--------+------+------+------+
(请注意，现在每个y值有一行。)好了，我们快到了!我们只需要去掉那些难看的null。
第四步:美化。我们只是将null值替换为0，这样结果集看起来会更好:
create view history_itemvalue_pivot_pretty as (
select
hostid,
coalesce(A, 0) as A,
coalesce(B, 0) as B,
coalesce(C, 0) as C
from history_itemvalue_pivot
);
select * from history_itemvalue_pivot_pretty;
+--------+------+------+------+
| hostid | A | B | C |
+--------+------+------+------+
| 1 | 10 | 3 | 0 |
| 2 | 9 | 0 | 40 |
+--------+------+------+------+
我们用MySQL构建了一个漂亮的数据透视表。
应用此程序时的注意事项:
what value to use in the extra columns. I used itemvalue in this example
what "neutral" value to use in the extra columns. I used NULL, but it could also be 0 or "", depending on your exact situation
what aggregate function to use when grouping. I used sum, but count and max are also often used (max is often used when building one-row "objects" that had been spread across many rows)
using multiple columns for y-values. This solution isn't limited to using a single column for the y-values -- just plug the extra columns into the group by clause (and don't forget to select them)
已知的限制:
这个解决方案不允许在数据透视表中有n个列——每个主列都需要在扩展基表时手动添加。对于5或10个x值，这个解很好。100美元，不太好。有一些存储过程生成查询的解决方案，但它们很难看，而且很难正确使用。当数据透视表需要有很多列时，我目前不知道有什么好方法来解决这个问题。
