ORACLE数据库多行数据合并为1行的问题,急用
发布网友
发布时间：2022-04-09 03:23
共3个回答
热心网友
时间：2022-04-09 04:53
你看看是不是这样的，你数据排版太混乱了
select csrq,qyph,pczl,wm_concat(jyxmmc),wm_concat(jyz),xydj,je from 表名 group by csrq,qyph,pczl,xydj,je----------补充-----------
oracle跟sqlserver不一样，如果存储过程的话只能返回类似sqlserver中print那种
这样的话，不知道能符合你要求不
表名我起的test
create table test(CSRQ varchar2(10),
QYPH varchar2(20),
pczl number(10,4),
jyxmmc varchar2(10),
jyz number(10,4),
xydj number(10,4),
je number(10,4))
insert into test values ('2014-5-6','201405070026',111.2300,'硅',3.1260,1280.0000,954050.8544);
insert into test values ('2014-5-6','201405070026',111.2300,'碳',3.4580,1280.0000,954050.8544);
insert into test values ('2014-5-6','201405070026',111.2300,'磷',0.0770,1280.0000,954050.8544);
insert into test values ('2014-5-6','201405070026',111.2300,'镍',6.7010,1280.0000,954050.8544);
insert into test values ('2014-5-6','201405070026',111.2300,'铬',2.5940,1280.0000,954050.8544);
insert into test values ('2014-5-6','201405070026',111.2300,'硫',0.2020,1280.0000,954050.8544);
执行
union all
select to_char(csrq),to_char(qyph),to_char(pczl),replace(to_char(wm_concat(jyz)),',',' '),to_char(xydj),to_char(je) from test
group by to_char(csrq),to_char(qyph),to_char(pczl),to_char(xydj),to_char(je)
结果
热心网友
时间：2022-04-09 06:11
sum(case when JYXMMC='硅' then JYZ else 0) as 硅,
sum(case when JYXMMC='碳' then JYZ else 0) as 碳
......
from table_name
group by CSRQ,QYPH,PCZL
;
行转列就是这样,枚举值有多少写多少追问枚举值是活的，不是固定的，怎么实现啊？
追答这个一条sql肯定是写不出来的,要用到plsql块来实现,可能有错误,你编译调试调试,哪有问题就自己修改下,只能帮你到这个份上了
declare
cursor get_JYXMMC is
select distinct JYXMMC
from table_name ;
v_jyx varchar2(20);
v_sql varchar2(1000);
begin
open get_JYXMMC;
v_sql:='select CSRQ,QYPH,PCZL';
loop
fetch get_JYXMMC into v_jyx;
exit when get_JYXMMC%notfound;
v_sql:= v_sql || ',sum(case when JYXMMC=''' || v_jyx ||''' then JYZ else 0 end) as '|| v_jyx;
end loop;
close get_JYXMMC;
v_sql:= v_sql || ' from table_name group by CSRQ,QYPH,PCZL';
excute immediate v_sql
end;
热心网友
时间：2022-04-09 07:45
