如何在 MySQL 中将行转换为列？

MySQL 数据表行列转换详解

引言

将数据表中以行表示观测值、列表示属性的数据转换为观测值变为列的表，是一种非常有用的数据操作技巧。本文将逐步讲解如何在 MySQL 中实现这种转换。

步骤 1：选择目标列

确定将作为目标透视表中 y 值（结果表行）和 x 值（结果表列）的列。在本例中，“hostid” 将作为 y 值，“itemname” 将作为 x 值。

步骤 2：扩展基础表添加额外列

为每个 x 值，向基础表添加一个新列。使用 CASE 表达式根据 itemname 将这些列填充为相应的 itemvalue。

步骤 3：分组和聚合扩展表

按 y 值列对扩展表进行分组，并使用适当的函数（例如 SUM）对 x 值列进行聚合。

步骤 4：美化 (可选)

将聚合表中的任何 NULL 值替换为选定的中性值（例如 0），以创建视觉上更清晰的结果。

示例

考虑以下基础表：

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

创建透视表

-- 步骤 1：选择目标列
SELECT hostid, itemname, itemvalue
FROM history;

-- 步骤 2：扩展基础表添加额外列
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;

-- 步骤 3：分组和聚合扩展表
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;

-- 步骤 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 中有效地重塑表以将行转换为列。

以上是如何在 MySQL 中将行转换为列？的详细内容。更多信息请关注PHP中文网其他相关文章！