MySQL - 将行转换为列并使用字符串

Question

我已经阅读了这篇答案：MySQL - Rows to Columns，并且我已经能够将其应用到我的情况中，直到聚合函数（第三步）。

我正在尝试将以下表格中itemname的行转换为itemvalue的列：

hostid	itemname	itemvalue
1	address	12 street
2	email	so@gmail
3	name	legend

我使用了以下代码：

create view table_extended as (
  select
    history.*,
    case when itemname = "address" then itemvalue end as address,
    case when itemname = "email" then itemvalue end as email,
    case when itemname = "name" then itemvalue end as name
  from history
);

现在我有了table_extended：

主机ID	项目名	项目值	地址	电子邮件	名称
1	地址	12街道	12街道	NULL	NULL
1	电子邮件	so@gmail	NULL	so@gmail	NULL
1	名称	传奇	NULL	NULL	传奇

在第3步中，他使用sum来汇总扩展表，其中所有的值都是整数。我尝试创建另一个视图，使用以下代码，但由于这些是字符串，所以除了主机ID外，所有行都变成0。

create view history_itemvalue_pivot as (
  select
    hostid,
    sum(address) as address,
    sum(email) as email,
    sum(name) as name
  from history_extended
  group by hostid
);

它看起来像这样：

主机ID	地址	电子邮件	姓名
1	0	0	0

如何合并所有带有NULL的行以获得以下结果：

主机ID	地址	电子邮件	姓名
1	12街道	so@gmail	传奇

- Abdullah Rasheed

---

对于字符串列，请使用MAX而不是SUM！ - Jorge Campos

是的，因为MAX()将按排序规则进行比较，并将NULL从结果集中排除以折叠成单行。否则，看起来很好。 - Michael Berkowski

@JorgeCampos 在我的语句 case when itemname = "address" then itemvalue end as address, 中，你知道如果 address 行为空，我该如何分配 n/a 吗？ - Abdullah Rasheed

@inspired 你可以使用IFNULL函数。 - Jorge Campos

Answer 1

您可以使用 GROUP_CONCAT 字符串函数。该函数尝试连接在 GROUP BY 子句上匹配的字符串，并在遇到 NULL 值时忽略它。

SELECT hostid,
       GROUP_CONCAT(address)    AS address,
       GROUP_CONCAT(email)  AS email,
       GROUP_CONCAT(name)       AS name
FROM (SELECT history.*,
             CASE WHEN itemname = "address" THEN itemvalue END AS address,
             CASE WHEN itemname = "email" THEN itemvalue END AS email,
             CASE WHEN itemname = "name" THEN itemvalue END AS name
      FROM history
) history_extended
GROUP BY hostid

请点击此处查看演示。

注意：我发现在您第一次引用表格时，有三个不同的主机ID，而在后面的引用中，您只引用了一个主机ID。是否存在一些打字错误？

- lemon