求Oracle数据库的一条查询语句

[liuyjcel]

数据库表tab的结构如下:

cust proudct Date number Money
客户1 产品一 2009-01-11 32 64000
客户2 产品2 2009-02-11 32 64000
客户3 产品3 2009-03-11 32 64000
客户4 产品4 2009-04-11 32 64000
客户5 产品5 2009-05-11 32 64000

查询条件是1月到6月内的分析数据。要求结果如下：

cust proudct 1月(number) 2月(number) 3月(number) 4月(number) 5月(number)

如果查询期间只有三个月，那结果如下：
cust proudct 1月(number) 2月(number) 3月(number)

那位大侠指点下.

[jiangshun]

Oracle不会，帮顶

[palm_java]

行列转换

[liuyjcel]

那位指点一下。

[liusong_china]

with tab as 
(
select '客户1' cust,'产品1' product,'2009-01-11' cDate,32 num,64000 Money from dual
union all
select '客户2' cust,'产品2','2009-02-11',32,64000 from dual
union all
select '客户3' cust,'产品3','2009-03-11',32,64000 from dual
union all
select '客户4' cust,'产品4','2009-04-11',32,64000 from dual
union all
select '客户5' cust,'产品5','2009-05-11',32,64000 from dual
)
select cust,product,decode(substr(cDate,6,2),'01',num,0) "1月",decode(substr(cDate,6,2),'02',num,0) "2月",
decode(substr(cDate,6,2),'03',num,0) "3月",decode(substr(cDate,6,2),'04',num,0) "4月",decode(substr(cDate,6,2),'05',num,0) "5月"
from tab;

[liusong_china]

17:00:38 scott@TUNGKONG> with tab as
17:00:44   2  (
17:00:44   3  select '客户1' cust,'产品1' product,'2009-01-11' cDate,32 num,64000 Money from dual
17:00:44   4  union all
17:00:44   5  select '客户2' cust,'产品2','2009-02-11',32,64000 from dual
17:00:44   6  union all
17:00:44   7  select '客户3' cust,'产品3','2009-03-11',32,64000 from dual
17:00:44   8  union all
17:00:44   9  select '客户4' cust,'产品4','2009-04-11',32,64000 from dual
17:00:44  10  union all
17:00:44  11  select '客户5' cust,'产品5','2009-05-11',32,64000 from dual
17:00:44  12  )
17:00:44  13  select cust,product,decode(substr(cDate,6,2),'01',num,0) "1月",decode(substr(cDate,6,2),'02',num,0) "2月",
17:00:44  14  decode(substr(cDate,6,2),'03',num,0) "3月",decode(substr(cDate,6,2),'04',num,0) "4月",decode(substr(cDate,6,2),'05',num,0) "5月"
17:00:44  15  from tab;

CUST  PRODU        1月        2月        3月        4月        5月
----- ----- ---------- ---------- ---------- ---------- ----------
客户1 产品1         32          0          0          0          0
客户2 产品2          0         32          0          0          0
客户3 产品3          0          0         32          0          0
客户4 产品4          0          0          0         32          0
客户5 产品5          0          0          0          0         32

已用时间:  00: 00: 00.03

楼主怎么把这种帖子发到非技术区了。。。。。。。。。

[Raul_Gonzalez]

固定列LS方法就可行，如果是不确定列就要用到存储过程了

Oracle不确定列的行列转换：http://topic.csdn.net/u/20080416/11/910e40c1-60f1-441f-8b0f-19a969d30f77.html

[小灰狼W]

用过程拼出语句
for cur in (select to_char("Date",'mm')d,to_char(trunc("Date",'mm'),'mon')m from tab group by to_char("Date",'mm') order by d)
loop
sqlstr:=sqlstr||chr(10)||',sum(case when to_char("Date",'mm')='''||cur.d||''' then "number" end)"'||cur.m||'"';
end loop;

[小灰狼W]

拼语句参考
http://topic.csdn.net/u/20091019/11/67cd55a3-3f42-4db7-a3f8-91dd52a913cd.html
游标可以用上面我写的修改下