表中的字段如下：
单位 项目 金额
a xx 100
a yy 300
b xx 400
b yy 800
c xx 1200
c yy 200
想修改为下面的结构
单位 项目xx金额 项目yy金额
a 100 300
b 400 800
c 1200 200
------解决方案--------------------
- SQL code
with temp as(select 'a' dw, 'xx' xm, 100 je from dualunionselect 'a' dw, 'yy' xm, 300 je from dualunionselect 'b' dw, 'xx' xm, 400 je from dualunionselect 'b' dw, 'yy' xm, 800 je from dualunionselect 'c' dw, 'xx' xm, 1200 je from dualunionselect 'c' dw, 'yy' xm, 200 je from dual)select distinct maint.dw, (select je from temp subt where subt.dw=maint.dw and subt.xm='xx') xxje,(select je from temp subt where subt.dw=maint.dw and subt.xm='yy') yyjefrom temp maintorder by maint.dw
------解决方案--------------------
- SQL code
with temp as(select 'a' 单位,'xx' 项目,100 金额 from dualunion allselect 'a' 单位,'yy' 项目,300 金额 from dualunion allselect 'b' 单位,'xx' 项目,400 金额 from dualunion allselect 'b' 单位,'yy' 项目,800 金额 from dualunion allselect 'c' 单位,'xx' 项目,1200 金额 from dualunion allselect 'c' 单位,'yy' 项目,200 金额 from dual)select 单位,sum(xx) 项目xx金额,sum(yy) 项目yy金额 from(select 单位,decode(项目,'xx',sum(金额)) xx,decode(项目,'yy',sum(金额)) yy from temp group by 单位,项目) group by 单位 order by 单位
------解决方案--------------------
select a.单位, a.金额 as xx金额, b.金额 as yy金额
from table a
inner join table b
on a.单位 = b.单位
where a.项目 = 'XX' and a.项目 = 'YY'
------解决方案--------------------
select 单位,sum(decode(项目,'xx',金额,0)),sum(decode(项目,'yy',金额,0)) from 表 group by 单位;