MySQL-行到列

我将对解决该问题所要采取的步骤添加一个稍长且更详细的解释。如果时间太长，我深表歉意。

我将从您给出的基础开始，并用它来定义几个术语，这些术语将在本文的其余部分中使用。这将是基本表：

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 | +--------+------+------+------+ 该history.hostid列中的值将在数据透视表中成为y值。在值history.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 | +--------+----------+-----------+------+------+------+ 请注意，我们没有更改行数，只是添加了额外的列。还要注意NULLs 的模式-具有的行itemname = "A"的新列具有非Anull值，其他新列具有null值。

步骤3：将扩展表分组并汇总。我们需要group by 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的东西。

步骤4：美化。我们将用零替换任何空值，因此结果集看起来更好：

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构建了一个漂亮的漂亮数据透视表。

应用此过程时的注意事项：

在多余的列中使用什么值。我itemvalue在这个例子中使用 在多余的列中使用什么“中性”值。我用过NULL，但也可以是0或""，具体取决于您的具体情况 分组时使用什么聚合函数。我用sum，但count并max也经常使用（max构建跨越了很多行已经扩展一排“对象”时经常使用） 使用多个列作为y值。此解决方案不仅限于对y值使用单个列-只需将多余的列插入group by子句中（不要忘了它们select） 已知限制：

此解决方案不允许数据透视表中有n列-扩展基本表时，需要手动添加每个数据透视列。因此，对于5或10个x值，此解决方案很好。为100，不是很好。有一些存储过程生成查询的解决方案，但是它们很丑陋，很难正确处理。当数据透视表需要有很多列时，我目前不知道解决此问题的好方法。来源：stack overflow