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
都经常使用)
转载自:https://stackoverflow.com/questions/1241178/mysql-rows-to-columns