sql语句case when如何将一个列变为２个列
(MeterOrigValue)表里有六个列，分别是 departmentid(部门id),fid(水电表示id),useqty(水电使用数),time(采集时间),wprice(水单价),
electPrice(电单价)
fid里分２种:1表示水; 2表示电
select departmentid, case when fid='1' then sum(cast (useqty as decimal(8,2)))
else '0' end as 用水量,case when fid='15' then sum(cast (useqty as decimal(8,2)))
else '0' end as 用电量
from MeterOrigValue group by fid,departmentid
这样出来的数据会是
部门 用水量 用电量
1 0 25
2 12 0
3 33 0
1 12 0
2 0 15
3 0 66
想请问下有没有办法把用水，用电合并到一行，显示结果变成
部门 用水量 用电量
1 12 25
2 12 15
3 33 66
[最优解释]
select departmentid, sum(case when fid='1' then cast (useqty as decimal(8,2))
else '0' end) as 用水量,sum(case when fid='15' then cast (useqty as decimal(8,2))
else '0' end) as 用电量
from MeterOrigValue group by departmentid
[其他解释]
select departmentid, sum(case when fid='1' then cast (useqty as decimal(8,2))
else '0' end) as 用水量,sum(case when fid='15' then cast (useqty as decimal(8,2))
else '0' end) as 用电量
from MeterOrigValue group by departmentid
select departmentid, sum(case when fid='1' then sum(cast (useqty as decimal(8,2)))
else '0' end )as 用水量,sum(case when fid='15' then sum(cast (useqty as decimal(8,2)))
else '0' end) as 用电量
from MeterOrigValue
group by fid,departmentid
