oracle表行列转换的问题
在oracle中，原表如图所示
客户编号时间数量
106/015000
106/03200
106/15400
206/042000
206/201000
306/01300
306/152000
306/30500
406/026000
406/29600
时间是六月份
如今要转换为下表
客户编号06/0106/0206/0306/0406/05......06/2906/30
15000020000 ........00
200020000..............00
33000000..............0500
406000000..............6000

用存储过程怎样实现，跪求各位大侠给写出详细代码。




[解决办法]

SQL code

create table TANJOTEST(  STUDENT VARCHAR2(20),  COURSE  VARCHAR2(20),  SCORE   NUMBER);INSERT INTO tanjotest(STUDENT, COURSE,SCORE)VALUES('三', '文',80);INSERT INTO tanjotest(STUDENT, COURSE,SCORE)VALUES('三', '',90);INSERT INTO tanjotest(STUDENT, COURSE,SCORE)VALUES('三', '英',50);INSERT INTO tanjotest(STUDENT, COURSE,SCORE)VALUES('李四', '文',40);INSERT INTO tanjotest(STUDENT, COURSE,SCORE)VALUES('李四', '',50);INSERT INTO tanjotest(STUDENT, COURSE,SCORE)VALUES('李四', '英',60);INSERT INTO tanjotest(STUDENT, COURSE,SCORE)VALUES('王五', '',100);方法考：方法一：SELECT STUDENT,MAX(decode(COURSE,'文',SCORE,0)) 文,MAX(decode(COURSE,'',SCORE,0)) ,MAX(decode(COURSE,'英',SCORE,0)) 英,SUM(SCORE) totalFROM tanjotestGROUP BY STUDENT;方法二：SELECT STUDENT,MAX(decode(COURSE,'文',SCORE,0)) 文,MAX(decode(COURSE,'',SCORE,0)) ,MAX(decode(COURSE,'英',SCORE,0)) 英,SUM(SCORE) totalFROM tanjotestGROUP BY STUDENT;
[解决办法]
SQL code
--我感觉你这只是你总需求的一部分，因为你少了年份，或者说比如表里存的不只是6月份的数据，等等情况，你还没说清楚，--就针对你说的只有6月份数据，这里写的存储过程有个输入参数月份，方便你后续扩展create or replace procedure row_to_col_func(v_rq in varchar2,cur out sys_refcursor)as  sqlstr varchar2(3000):='select a.custno ';begin     for rs in (          with t as(            select to_date(v_rq,'mm') mon from dual            )          select to_char(mon+level-1,'mm/dd') new_rq          from t          connect by level<=to_char(last_day(mon),'dd')     )      loop          sqlstr:=sqlstr||chr(10)||','||'max(decode(a.rq,'''||rs.new_rq||''',a.quantity,0)) as "'||rs.new_rq||'"';     end loop ;     sqlstr:=sqlstr||chr(10)||'from tab a group by a.custno ' ;     open cur for sqlstr;end row_to_col_func;/