mysql 行转列 逗号隔开 mysql实现行转列

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

很显然（x-y坐标系来考虑），要将hostid列中的值作为唯一行标识（坐标y值），history.itemname列的值作为列名（坐标x值），hosid和itemname相对应的就是history.itemvalue的值(坐标点)。

思路：

• 选择确定你所需要的列，即y和x
• 使用额外的列扩展基本表（每一个x值为一个列）
• 分组并汇总扩展表（每一个y值为一组）

解决步骤：

• 步骤1：使用额外的列扩展history表（每一个itemname的值为一列）

create view history_extended as (
  select
    history.*,
    case when itemname = "A" then itemvalue else 0 end as A,
    case when itemname = "B" then itemvalue else 0 end as B,
    case when itemname = "C" then itemvalue else 0 end as C
  from history
);

select * from history_extended;

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

注意，我们并没有更改行数，只是添加了额外的列。（这里将hostid和itemname所对应（x-y坐标系）不存在的值(坐标点)设为了0，也可以设置为NULL或“”或特定值，根据业务情况而定）

• 步骤2：将扩展表分组并汇总。即 group by hostid

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	0
2	9	0	40

完成！

整合上述步骤如下：

select
    hostid,
    SUM(case when itemname = "A" then itemvalue else 0 end) as A,
    SUM(case when itemname = "B" then itemvalue else 0 end) as B,
    SUM(case when itemname = "C" then itemvalue else 0 end) as C
  from history
  group by hostid

---

注意事项：

• 确定在多余的列中你所要使用的值。
• 在多余的列中使用什么“中性”值。（NULL，0或''等等，具体取决于您的实际情况）
• 分组时使用什么聚合函数（sum，count和max都经常使用）

---