有这样一张表
zddm cjsj xdm xsj 这四个字段分别代表
站点代码 采集时间 数据项代码 数据项值
站点代码是一对多的关系,也就是说我想把同一个站点的所有数据项都取出来,插到另一张表里面以列的形式放入
ZDDM CJSJ XDM XSJ
1 2013/9/23 1110101 120
1 2013/9/23 1110102 50
1 2013/9/23 1110103 40
想弄成这样的
ZDDM CJSJ 1110101 1110102 1110103
1 2013/9/23 120 50 40
求高手帮助 oraclesql存储过程
zddm cjsj xdm xsj 这四个字段分别代表
站点代码 采集时间 数据项代码 数据项值
站点代码是一对多的关系,也就是说我想把同一个站点的所有数据项都取出来,插到另一张表里面以列的形式放入
ZDDM CJSJ XDM XSJ
1 2013/9/23 1110101 120
1 2013/9/23 1110102 50
1 2013/9/23 1110103 40
想弄成这样的
ZDDM CJSJ 1110101 1110102 1110103
1 2013/9/23 120 50 40
求高手帮助 oraclesql存储过程
如下:with ax as(
select 1 ZDDM, '2013/9/23' CJSJ, '1110101' XDM, '120' XSJ from dual union all
select 1 ZDDM, '2013/9/23' CJSJ, '1110102' XDM, '50' XSJ from dual union all
select 1 ZDDM, '2013/9/23' CJSJ, '1110103' XDM, '40' XSJ from dual
)
select ZDDM,CJSJ,
max(case when XDM='1110101' then XSJ end) a,
max(case when XDM='1110102' then XSJ end) b,
max(case when XDM='1110103' then XSJ end) c
from ax
group by ZDDM,CJSJ; ZDDM CJSJ A B C
---------- --------- --- --- ---
1 2013/9/23 120 50 40
sum(DECODE(XDM,1110102,XSJ,NULL)) AS a1110102,
sum(DECODE(XDM,1110103,XSJ,NULL)) AS a1110103
FROM t GROUP BY ZDDM,CJSJ
max(case XDM when 1110101 then XSJ else 0 end) as 1110101,
max(case XDM when 1110102 then XSJ else 0 end) as 1110102,
max(case XDM when 1110103 then XSJ else 0 end) as 1110103
from table group by ZDDM,CJSJ,1110101,1110102,1110103试下吧,反正是用case when then这种语法,不行的话你在改改
(
select '1' as ZDDM,'2013/9/23' as CJSJ,'1110101' as XDM ,'120' as XSJ from dual union all
select '1' as ZDDM,'2013/9/23' as CJSJ,'1110102' as XDM ,'50' as XSJ from dual union all
select '1' as ZDDM,'2013/9/23' as CJSJ,'1110103' as XDM ,'40' as XSJ from dual
)
select zddm,cjsj,
max(decode(XDM,'1110101',XSJ,0)) as "1110101",
max(decode(XDM,'1110102',XSJ,0)) as "1110102",
max(decode(XDM,'1110103',XSJ,0)) as "1110103"
from a group by cjsj,zddm