多行转多列，行数和列数不确定

最新推荐文章于 2024-03-05 17:49:24 发布

开心就是最好

最新推荐文章于 2024-03-05 17:49:24 发布

阅读量1.3k

点赞数

分类专栏： SQL 文章标签：数据库开发技术数据库

SQL 专栏收录该内容

20 篇文章 2 订阅

订阅专栏

参考：http://blog.itpub.net/94041/viewspace-965866/

分类：数据库开发技术

原始需求，有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;

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;

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;

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

[@more@]