除表tt
drop table tt;
/Top
8 楼wzs_wzs123（蓝天百云）回复于 2004-11-04 12:36:02 得分 0
yjdn(无尽天空) ，兄弟，给我一个能够直接运行的SQl语句，我对ORacle不太熟Top
9 楼yjdn（文刀无尽）回复于 2004-11-04 12:46:59 得分 50
MSSQL语句？
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',sum(case km when '''+rtrim(km)+''' then cj else 0 end) as ['+rtrim(km)+']'
from test
group by km
exec('select name'+@sql+' from test group by name')
--那你的建表语句还得改一下，把number改成int
col1 col2
-----------------
0001 Tom
0001 Jack
0001 Mike
0002 Kate
0002 Rose
0002 Fene
如何用一条SQL语句得到如下结果：
col1 col2
-----------------
0001 Tom,Jack,Mike
0002 Kate,Rose,Fene
创建自定义函数：
create or replace function f_ename(jobno in varchar2)
return varchar2 is
str_return varchar2(200);
cursor c_ename(v_jobno varchar2) is select ename from emp where job=v_jobno;
begin
for str_temp in c_ename(jobno) loop
str_return:=str_return||str_temp.ename;
end loop;
return str_return;
end;
/
调用：
select job,f_ename(job) from (select distinct job from emp);
作者“jadewoo”