原始需求，有2表如下
SQL> select * from mas;

TO TOOLNAME
-- ----------
01 包裹
02 信函
03 挂号信
04 中国速递
05 EMS
06 DHL

6 rows selected.

SQL> select * from putdt;

SENDDT TO GNAME ST CITY
-------- -- ---------- -- ----
20070101 01 john 12 2566
20070101 04 david 16 3098
20070101 05 apple 18 2098
20070201 04 apple 16 3078
20070304 05 poly 13 1001
20070304 04 john 12 2566
20070101 01 james 12 8800

7 rows selected.

要求输出如下格式的报表
senddt toolid1 sum1 toolid2 sum2 toolid3 sum3 toolid4 sum4 toolid5 sum5
20070101 01 1 02 0 03 0 04 1 05 1
20070201 01 0 02 0 03 0 04 1 05 0
20070304 01 0 02 0 03 0 04 1 05 1
即以senddt和toolid分组后的count数
每行显示一个senddt，但是列数是不确定的，根据表mas的toolid来定的

基本思路如下，如下的SQL 是动态构造的，其中的列数不确定，根据mas的toolid来定
select senddt,
max(toolid1) toolid1,max(sum1) sum1,
max(toolid2) toolid2,max(sum2) sum2,
max(toolid3) toolid3,max(sum3) sum3,
max(toolid4) toolid4,max(sum4) sum4,
max(toolid5) toolid5,max(sum5) sum5
from (select b.senddt,
'01' toolid1,
case when a.toolid=b.toolid and b.toolid='01' then b.sum_c else 0 end sum1,
'02' toolid2,
case when a.toolid=b.toolid and b.toolid='02' then b.sum_c else 0 end sum2,
'03' toolid3,
case when a.toolid=b.toolid and b.toolid='03' then b.sum_c else 0 end sum3,
'04' toolid4,
case when a.toolid=b.toolid and b.toolid='04' then b.sum_c else 0 end sum4,
'05' toolid5,
case when a.toolid=b.toolid and b.toolid='05' then b.sum_c else 0 end sum5
from mas a,
(select senddt,toolid,count(*) sum_c from putdt group by senddt,toolid) b)
group by senddt
order by senddt

测试用的存储过程
create or replace procedure test
as
v_column varchar2(5) := '';
v_sql_0 varchar2(10000) := '';
v_sql_1 varchar2(10000) := '';
p_fields varchar2(10000) := '';
begin
for t in (select toolid from mas order by toolid) loop
v_column := to_char(to_number(t.toolid));
v_sql_0 := v_sql_0||',max(toolid'||v_column||') toolid'||v_column||',max(sum'||v_column||') sum'||v_column;
v_sql_1 := v_sql_1||','''||t.toolid||''' toolid'||v_column||', case when a.toolid=b.toolid and b.toolid='''||t.toolid||''' then b.sum_c else 0 end sum'||v_column;
p_fields := p_fields||',toolid'||v_column||',sum'||v_column;
end loop;
v_sql_1 := 'select senddt'||v_sql_0||' from (select b.senddt'||v_sql_1||' from mas a,(select senddt,toolid,count(*) sum_c from putdt group by senddt,toolid) b) group by senddt order by senddt';
p_fields := ltrim(p_fields,',');
--open p_rs for v_sql_1;
dbms_output.put_line(v_sql_1);
end;
/

--------------
1. 建立package
--------------

create or replace package pkg_test
as
type cursor_fount100 is ref cursor;
procedure rs_fount100 (p_fields in out varchar2,p_rs in out cursor_fount100);
end;
/

create or replace package body pkg_test
as
procedure rs_fount100 (p_fields in out varchar2,p_rs in out cursor_fount100)
is
v_column varchar2(5) := '';
v_sql_0 varchar2(10000) := '';
v_sql_1 varchar2(10000) := '';
begin
for t in (select toolid from mas order by toolid) loop
v_column := to_char(to_number(t.toolid));
v_sql_0 := v_sql_0||',max(toolid'||v_column||') toolid'||v_column||',max(sum'||v_column||') sum'||v_column;
v_sql_1 := v_sql_1||','''||t.toolid||''' toolid'||v_column||', case when a.toolid=b.toolid and b.toolid='''||t.toolid||''' then b.sum_c else 0 end sum'||v_column;
p_fields := p_fields||',toolid'||v_column||',sum'||v_column;
end loop;
v_sql_1 := 'select senddt'||v_sql_0||' from (select b.senddt'||v_sql_1||' from mas a,(select senddt,toolid,count(*) sum_c from putdt group by senddt,toolid) b) group by senddt order by senddt';
p_fields := ltrim(p_fields,',');
open p_rs for v_sql_1;
end;
end;
/

----------------------------
2. JAVA程序 test.java 演示用
----------------------------

import java.io.*;
import java.text.*;
import java.lang.String;
import java.util.*;
import java.util.regex.*;
import java.lang.*;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;

public class test
{
public static void main(String[] args) throws Exception
{
String record_str="";
String field_list="";
String[] fields=null;
Pattern p = Pattern.compile(",");
Matcher m1;
OracleCallableStatement cstmt = null;
ResultSet rs = null;
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.1:1521:实例名","数据库用户","密码");
cstmt = (OracleCallableStatement)conn.prepareCall("begin pkg_test.rs_fount100(?,?); end;");
cstmt.registerOutParameter(1,OracleTypes.VARCHAR);
cstmt.registerOutParameter(2,OracleTypes.CURSOR);
cstmt.execute();
field_list = cstmt.getString(1);
rs = (ResultSet)cstmt.getObject(2);
fields=p.split(field_list);
for(int i=0; i<fields.length; i++)
{
record_str=record_str+fields[i]+" ";
}
System.out.println("senddt "+record_str);
while (rs.next())
{
record_str="";
for(int f=1; f<=fields.length+1; f++)
{
record_str=record_str+rs.getString(f)+" ";
}
System.out.println(record_str);
}
rs.close();
cstmt.close();
conn.close();
}
}

---------------
3. 编译java程序
---------------
javac test.java

---------------
4. 演示1
---------------

SQL> select * from mas;

TO TOOLNAME
-- ----------
01 包裹
02 信函
03 挂号信
04 中国速递
05 EMS

SQL> select * from putdt;

SENDDT TO GNAME ST CITY
-------- -- ---------- -- ----
20070101 01 john 12 2566
20070101 04 david 16 3098
20070101 05 apple 18 2098
20070201 04 apple 16 3078
20070304 05 poly 13 1001
20070304 04 john 12 2566

6 rows selected.

E:>java test
senddt toolid1 sum1 toolid2 sum2 toolid3 sum3 toolid4 sum4 toolid5 sum5
20070101 01 1 02 0 03 0 04 1 05 1
20070201 01 0 02 0 03 0 04 1 05 0
20070304 01 0 02 0 03 0 04 1 05 1

---------------
5. 演示2
---------------

SQL> insert into putdt values('20070101','01','james','12','8800');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from putdt;

SENDDT TO GNAME ST CITY
-------- -- ---------- -- ----
20070101 01 john 12 2566
20070101 04 david 16 3098
20070101 05 apple 18 2098
20070201 04 apple 16 3078
20070304 05 poly 13 1001
20070304 04 john 12 2566
20070101 01 james 12 8800

7 rows selected.

E:>java test
senddt toolid1 sum1 toolid2 sum2 toolid3 sum3 toolid4 sum4 toolid5 sum5
20070101 01 2 02 0 03 0 04 1 05 1
20070201 01 0 02 0 03 0 04 1 05 0
20070304 01 0 02 0 03 0 04 1 05 1

---------------
6. 演示3
---------------
SQL> insert into mas values('06','DHL');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from mas;

TO TOOLNAME
-- ----------
01 包裹
02 信函
03 挂号信
04 中国速递
05 EMS
06 DHL

6 rows selected.

SQL> select * from putdt;

SENDDT TO GNAME ST CITY
-------- -- ---------- -- ----
20070101 01 john 12 2566
20070101 04 david 16 3098
20070101 05 apple 18 2098
20070201 04 apple 16 3078
20070304 05 poly 13 1001
20070304 04 john 12 2566
20070101 01 james 12 8800

7 rows selected.

E:java test
senddt toolid1 sum1 toolid2 sum2 toolid3 sum3 toolid4 sum4 toolid5 sum5 toolid6 sum6
20070101 01 2 02 0 03 0 04 1 05 1 06 0
20070201 01 0 02 0 03 0 04 1 05 0 06 0
20070304 01 0 02 0 03 0 04 1 05 1 06 0

中国refractometer总库是一个refractometer最专业最系统的refractometerB 2B商务平台，refractometer采用“网上refractometer线下，refractometer双网合一”上海refrigeration compressor公司是上海地区专业的refrigeration compressor公司.欢迎联系上海refrigeration compressor公司的refrigeration compressor服务,作为上海知名refrigeration compressor公司之一河南Replica chanel handbags,河南Replica chanel handbags资讯,河南酒店,河南Replica chanel handbags交通,河南Replica chanel handbags景点河南旅行社,洛阳Replica chanel handbagsReplica chanel handbags依托领先的Replica chanel handbags发布技术和Replica chanel handbags优化技术，iPlus建立了Replica chanel handbags效果评测体系，不断提高Replica chanel handbags联盟网站的收入。深圳replica handbag公司是由知名深圳replica handbag公司、北京replica handbag公司，广州replica handbag公司，东莞replica handbag公司双人replica nike(北京replica nike/上海replica nike/广州replica nike/深圳replica nike),replica nike是一家专业replica nike