我试图搜索帖子，但我只找到了 SQL Server/Access 的解决方案。我需要 MySQL (5.X) 中的解决方案。

我有一个包含 3 列的表(称为历史记录):hostid、itemname、itemvalue。
如果我进行选择(select * from history)，它将返回

   +--------+----------+-----------+
   | hostid | itemname | itemvalue |
   +--------+----------+-----------+
   |   1    |    A     |    10     |
   +--------+----------+-----------+
   |   1    |    B     |     3     |
   +--------+----------+-----------+
   |   2    |    A     |     9     |
   +--------+----------+-----------+
   |   2    |    C     |    40     |
   +--------+----------+-----------+

如何查询数据库以返回类似的内容

   +--------+------+-----+-----+
   | hostid |   A  |  B  |  C  |
   +--------+------+-----+-----+
   |   1    |  10  |  3  |  0  |
   +--------+------+-----+-----+
   |   2    |   9  |  0  |  40 |
   +--------+------+-----+-----+

最佳答案

我将添加一个更长更详细的说明来解决这个问题。如果太长，我很抱歉。

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

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-values(原因很明显)。

当我必须解决创建数据透视表的问题时，我使用三步过程(可选的第四步)来解决它:

1. 选择感兴趣的列，即y-values和x-values
2. 用额外的列扩展基表 - 每个 x-value
列
3. 对扩展表进行分组和聚合 -- 每个 y 值
一组
4. (可选)美化聚合表

让我们将这些步骤应用于您的问题，看看我们得到了什么:

第 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" 的行对于新列 A 具有非空值，并且其他新列的 null 值。

第 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。

第 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，不是很好。有一些使用存储过程生成查询的解决方案，但它们很难看且难以正确处理。当数据透视表需要有很多列时，我目前不知道解决此问题的好方法。

关于MySQL - 行到列，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/1241178/