數據 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) total FROM tanjotest GROUP BY STUDENT;方法二:SELECT STUDENT, MAX(decode(COURSE,'語文',SCORE,0)) 語文, MAX(decode(COURSE,'數學',SCORE,0)) 數學, MAX(decode(COURSE,'英語',SCORE,0)) 英語, SUM(SCORE) total FROM tanjotest GROUP BY STUDENT;
方法二: SELECT STUDENT, SUM(case COURSE when '語文' then SCORE else 0 end) 語文, SUM(case COURSE when '數學' then SCORE else 0 end) 數學, SUM(case COURSE when '英語' then SCORE else 0 end) 英語, SUM(SCORE) total FROM tanjotest GROUP BY STUDENT;
--我感觉你这只是你总需求的一部分,因为你少了年份,或者说比如表里存的不只是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; /
在oracle中,原表如下表所示:
客户编号 时间 数量
1 06/01 5000
1 06/03 200
1 06/15 400
2 06/04 2000
2 06/20 1000
3 06/01 300
3 06/15 2000
3 06/30 500
4 06/02 6000
4 06/29 600
时间是六月份
如今要转换为下表
客户编号 06/01 06/02 06/03 06/04 06/05 ...... 06/29 06/30
1 5000 0 200 0 0 ....... 0 0
2 0 0 0 2000 0 ....... 0 0
3 300 0 0 0 0 ....... 0 500
4 0 6000 0 0 0 ....... 600 0表转换后,时间从06/01到06/30,没有订单数量的表格补零,用存储过程怎样实现,跪求各位大侠给写出详细代码。重要的是要用存储过程啊。
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) total
FROM tanjotest
GROUP BY STUDENT;方法二:SELECT STUDENT,
MAX(decode(COURSE,'語文',SCORE,0)) 語文,
MAX(decode(COURSE,'數學',SCORE,0)) 數學,
MAX(decode(COURSE,'英語',SCORE,0)) 英語,
SUM(SCORE) total
FROM tanjotest
GROUP BY STUDENT;
SELECT STUDENT,
SUM(case COURSE when '語文' then SCORE else 0 end) 語文,
SUM(case COURSE when '數學' then SCORE else 0 end) 數學,
SUM(case COURSE when '英語' then SCORE else 0 end) 英語,
SUM(SCORE) total
FROM tanjotest
GROUP BY STUDENT;
--就针对你说的只有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;
/