oracle 10g pl/sql 行转列问题

[凌音]

编写 SQL 语句保存为 .sql 文件运行，查询各产品的季度销售额，查询结果包括：产品名称、订购年份、第1季度销售额、第2季度销售额、第3季度销售额、第4季度销售额；

这个是不是要用到decode 、case 、when、then 、end 什么的啊，要怎么写啊 ，我查出来的都没按产品名称分类
select p.ProName,extract(year from OrderDate) as OYEAR,
DECODE ( to_char( o.OrderDate,'q'), '1', to_char(sum(s.Price * s.Num * ( 1- s.Disconut )))) one,
DECODE ( to_char( o.OrderDate,'q'), '2', to_char(sum(s.Price * s.Num * ( 1- s.Disconut )))) two,
DECODE ( to_char( o.OrderDate,'q'), '3', to_char(sum(s.Price * s.Num * ( 1- s.Disconut )))) three,
DECODE(to_char(o.OrderDate,'q'),'4', to_char(sum(s.Price * s.Num * ( 1- s.Disconut )))) four
from Class c full outer join Product p
on c.ClassID=p.ClassID full outer join SaleOrderItem s on
p. ProID=s.ProductID full outer join Orders o
on s.OrderID=o.OrdersID
group by rollup (p.ProName,to_char(o.OrderDate,'q'),extract(year from OrderDate) , s.Price,s.Num,s.Disconut)
表结构
create table Orders
(
OrdersID number(7) not null,
CusID varchar2(5),
EmpID number,
OrderDate DATE,
ArrivalDate DATE,
DispatchDate DATE,
Deliverier varchar2(20),
Deliveryfee number(7,2),
HostName varchar2(40),
HostAddress varchar2(60),
HostCity varchar2(25),
HostArea varchar2(25),
HostPC varchar2(20),
HostState varchar2(25)
);
create table Product
(
ProID NUMBER not null,
ProName VARCHAR2(20),
SupID NUMBER,
ClassID NUMBER,
UnitNum VARCHAR2(20),
Price number(7,2),
Stock NUMBER,
Orders NUMBER,
Reorders NUMBER,
Stop VARCHAR2(5) not null
)
create table SaleOrderItem
(
OrderID number,
ProductID number,
Price number(7,2),
Num number,
Disconut number(7,2)
);

[opps_zhou]

你干嘛要用 group by rollup ，你要分类汇总？

[凌音]

按产品分类，按季度汇总销售额

[opps_zhou]

那你不需要使用 group by rollup 把，直接 group by 产品名称，年，季度，将按产品和时间的数据先汇总好，然后再对汇总好的数据进行 行转列
行转列，你写的语句基本正确