现有数据:
bj code sl
1 001 1
1 002 2
1 003 3
2 001 4
2 002 5
2 003 6
3 001 7
3 002 8
3 003 9
如何利用sql语句实现:
bj 001 002 003
1 1 2 3
2 4 5 6
3 7 8 9
bj code sl
1 001 1
1 002 2
1 003 3
2 001 4
2 002 5
2 003 6
3 001 7
3 002 8
3 003 9
如何利用sql语句实现:
bj 001 002 003
1 1 2 3
2 4 5 6
3 7 8 9
参考--测试数据
create table t (XH varchar2(10), DDATE date, SXF int);
insert into t
select 1,sysdate,10 from dual union all
select 1,sysdate+1,14 from dual union all
select 1,sysdate+2,23 from dual union all
select 2,sysdate,21 from dual union all
select 2,sysdate+1,24 from dual union all
select 3,sysdate,13 from dual union all
select 3,sysdate+1,22 from dual;
--
create or replace package sp_test
is
type ResultData is ref cursor;
procedure getRstData( rst out ResultData);
end sp_test;
/
create or replace package body sp_test
is
procedure getRstData( rst out ResultData)
is
begin
declare
cursor cur is select distinct (DDATE) from t;
tmp_ddate date;
str varchar2(4000);
begin
str:='select xh';
open cur;
loop
fetch cur into tmp_ddate;
exit when cur%notfound;
str:=str||',sum(decode(to_char(ddate,''yyyymmdd''),'||chr(39)||to_char(tmp_ddate,'yyyymmdd')||chr(39)||',sxf,0)) "'||to_char(tmp_ddate,'yyyymmdd')||'"';
end loop;
str:=str||' from t group by xh';
-- dbms_output.put_line(str);
close cur;
open rst for str;
end;
end;
end sp_test;
/--输出结果
1 10 14 23
2 21 24 0
3 13 22 0
--你的应该是这样了--测试数据
create table t (XH varchar2(10), DDATE varchar2(100), SXF int);
insert into t
select 1,'001',10 from dual union all
select 1,'002',14 from dual union all
select 1,'003',23 from dual union all
select 2,'001',21 from dual union all
select 2,'002',24 from dual union all
select 2,'003',24 from dual union all
select 3,'001',21 from dual union all
select 3,'002',24 from dual union all
select 3,'003',13 from dual;
--
create or replace package sp_test
is
type ResultData is ref cursor;
procedure getRstData( rst out ResultData);
end sp_test;
/
create or replace package body sp_test
is
procedure getRstData( rst out ResultData)
is
begin
declare
cursor cur is select distinct (DDATE) from t;
tmp_ddate varchar2(100);
str varchar2(4000);
begin
str:='select xh';
open cur;
loop
fetch cur into tmp_ddate;
exit when cur%notfound;
str:=str||',sum(decode(ddate,'''||tmp_ddate||''',sxf,0)) ';
end loop;
str:=str||' from t group by xh';
-- dbms_output.put_line(str);
close cur;
open rst for str;
end;
end;
end sp_test;
/
FROM table_name
GROUP BY bj;这样看看,10g版本可以用WMSYS.WM_CONCAT函数!
不定列就用hongqi162写的