declare @sql varchar(500)
set @sql = 'select country '
select @sql = @sql + ' , sum(case aihao when ''' + aihao + ''' then renshu else 0 end) [' + aihao + ']'
from (select distinct aihao from F) as a
set @sql = @sql + ' from F group by country'
exec(@sql)
set @sql = 'select country '
select @sql = @sql + ' , sum(case aihao when ''' + aihao + ''' then renshu else 0 end) [' + aihao + ']'
from (select distinct aihao from F) as a
set @sql = @sql + ' from F group by country'
exec(@sql)
v_sql varchar(500);
begin
v_sql := 'select country ';
select v_sql || ' , sum(case aihao when ''' || aihao || ''' then renshu else 0 end) [' || aihao || ']' into v_sql
from (select distinct aihao from F) as a ;
v_sql := v_sql || ' from F group by country' ;
execute immediate v_sql;
end;
如果是的话应该这样
declare
v_sql varchar(500);
begin
v_sql := 'select country ';
select v_sql || ' , sum(case aihao when ''' || aihao || ''' then renshu else 0 end) as ' || aihao into v_sql
from (select distinct aihao from F) as a ;
v_sql := v_sql || ' from F group by country' ;
execute immediate v_sql;
end;
[country] [nchar](10) NULL,
[didian] [nchar](10) NULL,
[aihao] [nchar](10) NULL,
[renshu] [int] NULL
) ON [PRIMARY]
这我是创建测试表F的脚本。。你拿去测试
select * from F
insert into F values('中国','A', '读书', 20)
insert into F values('中国','B', '读书', 30)
insert into F values('中国','A', '玩火', 10)
insert into F values('中国','A', '斗地主', 40)
insert into F values('南非','A', '读书', 40)
insert into F values('南非','A', '读书', 20)
insert into F values('南非','A', '读书', 30)
insert into F values('南非','A', '斗地主', 10)
insert into F values('南非','A', '斗地主', 20)
insert into F values('美国','A', '玩火', 10)
insert into F values('美国','A', '读书', 20)
insert into F values('美国','A', '玩火', 30)这是我的数据,里面的国家,和爱好是不固定的
country 斗地主 读书 玩火 爱好可以很多......
美国 NULL 20 40 .......
南非 30 90 NULL ........
中国 40 50 10 ........
国家也可以很多.................................
.........................................
.....................................
这是我要的结果.
在SQL2000下的语句可以实现:declare @sql varchar(500)
set @sql = 'select country '
select @sql = @sql + ' , sum(case aihao when ''' + aihao + ''' then renshu else 0 end) [' + aihao + ']'
from (select distinct aihao from F) as a
set @sql = @sql + ' from F group by country'
exec(@sql)
要的是ORACLE下怎么来写?
declare
*第 1 行出现错误:
ORA-01422: 实际返回的行数超出请求的行数 ORA-06512: 在
line 5
cursor cr is select distinct aihao from f;
v_aihao varchar2(10);
v_sql varchar2(500);
begin
v_sql := 'select country ';
open cr;
fetch cr into v_aihao;
while cr %found loop
v_sql:=v_sql||' , sum(case aihao when ''' || v_aihao || ''' then renshu else 0 end) as ' || v_aihao;
fetch cr into v_aihao;
end loop;
v_sql:=v_sql || ' from F group by country' ;
execute immediate v_sql;
end;
*第 1 行出现错误:
ORA-12704: 字符集不匹配
ORA-06512: 在 line 14
是不是里面有空记录还是什么?
我的表结构是这样的
SQL> desc f
名称 是否为空?类型
------------------------------- -------- ----
COUNTRY VARCHAR2(10)
DIDIAN VARCHAR2(10)
AIHAO VARCHAR2(10)
RENSHU NUMBER
declare
cursor cr is select distinct aihao from f where aihao is not null;
v_aihao varchar2(10);
v_sql varchar2(500);
begin
v_sql := 'select country ';
open cr;
fetch cr into v_aihao;
while cr %found loop
v_sql:=v_sql||' , sum(case aihao when ''' || v_aihao || ''' then renshu else 0 end) as ' || v_aihao;
fetch cr into v_aihao;
end loop;
v_sql:=v_sql || ' from F group by country' ;
execute immediate v_sql;
end;
问一个比较小白的问题,我没有用过过程,怎么调过它出来结果?
我是第一个用过程,刚开始用ORACLE,只会单语句。
谢谢了
country 斗地主 读书 玩火 爱好可以很多......
美国 NULL 20 40 .......
南非 30 90 NULL ........
中国 40 50 10 ........
国家也可以很多.................................
.........................................
.....................................
-------------------- --------- --------- ---------
美国 0 20 40
南非 30 90 0
中国 40 50 10