1: 列转为行:
eg1:
Create table test (name char(10),km char(10),cj int);
go
insert into test values('张三','语文',80);
insert into test values('张三','数学',86);
insert into test values('张三','英语',75);
insert into test values('李四','语文',78);
insert into test values('李四','数学',85);
insert into test values('李四','英语',78);想变成姓名 语文 数学 英语
张三 80 86 75
李四 78 85 78create or replace procedure hlzh is
ssql varchar2(8000);
begin
ssql:='select name';
select ssql:=concat(ssql,',(case km when a.km then cj end case;) as km')
from (select distinct km from test) as a;
select ssql:=concat(ssql,' from test group by name');
ssql;
end hlzh;
在plsql developer按F8,出现错误提示:PROCEDURE SYS.HLZH 编译错误错误: PL/SQL: ORA-00923: 未找到预期 FROM 关键字
行: 5
文本: select ssql:=concat(ssql,',(case km when a.km then cj end case;) as km')错误: PL/SQL: SQL Statement ignored
行: 5
文本: select ssql:=concat(ssql,',(case km when a.km then cj end case;) as km')错误: PL/SQL: ORA-00923: 未找到预期 FROM 关键字
行: 7
文本: select ssql:=concat(ssql,' from test group by name');错误: PL/SQL: SQL Statement ignored
行: 7
文本: select ssql:=concat(ssql,' from test group by name');这是一个行列转换的的问题,原版语句是别人发布在sqlserver上用的,现在将他改成在oracle下使用,我不知道哪里改错了,请指教?
eg1:
Create table test (name char(10),km char(10),cj int);
go
insert into test values('张三','语文',80);
insert into test values('张三','数学',86);
insert into test values('张三','英语',75);
insert into test values('李四','语文',78);
insert into test values('李四','数学',85);
insert into test values('李四','英语',78);想变成姓名 语文 数学 英语
张三 80 86 75
李四 78 85 78create or replace procedure hlzh is
ssql varchar2(8000);
begin
ssql:='select name';
select ssql:=concat(ssql,',(case km when a.km then cj end case;) as km')
from (select distinct km from test) as a;
select ssql:=concat(ssql,' from test group by name');
ssql;
end hlzh;
在plsql developer按F8,出现错误提示:PROCEDURE SYS.HLZH 编译错误错误: PL/SQL: ORA-00923: 未找到预期 FROM 关键字
行: 5
文本: select ssql:=concat(ssql,',(case km when a.km then cj end case;) as km')错误: PL/SQL: SQL Statement ignored
行: 5
文本: select ssql:=concat(ssql,',(case km when a.km then cj end case;) as km')错误: PL/SQL: ORA-00923: 未找到预期 FROM 关键字
行: 7
文本: select ssql:=concat(ssql,' from test group by name');错误: PL/SQL: SQL Statement ignored
行: 7
文本: select ssql:=concat(ssql,' from test group by name');这是一个行列转换的的问题,原版语句是别人发布在sqlserver上用的,现在将他改成在oracle下使用,我不知道哪里改错了,请指教?
sum(decode(km,'语文',cj,0)) 语文,
sum(decode(km,'数学',cj,0)) 数学,
sum(decode(km,'英语',cj,0)) 英语
from test group by name
select ssql:=concat(ssql,',(case km when a.km then cj end case;) as km')
from (select distinct km from test) as a;
我倾向于select distinct km from test作为游标,然后在循环中拼串
from (select distinct km from test) as a ;
from test group by name第三、procedure中不支持直接select * from table;第四、连接字符串直接用 string_a || '字符串文本' || string_b 即可第五、测试前先用dbms_output.put_line(ssql);打印出来看看语句对不对。前提是set serveroutput on;