如何在 Oracle 10g 中进行数据透视
考虑以下
示例输入
SalesBoyName Product Amount
------------ ------- ------
Boy1 P1 100
Boy1 P1 40
Boy1 P2 100
Boy2 P1 100
Boy2 P3 12
期望的输出
SalesBoyName P1 P2 P3
------------ ---- ---- ----
Boy1 140 100 null
Boy2 100 null 12
下面的 SQL SERVER 2005 查询将完成工作
The below SQL SERVER 2005 query will do the work
SELECT SalesBoyName, [P1] AS P1, [P2] AS P2,[P3] AS P3
FROM
(SELECT * FROM tblSales ) s
PIVOT
(
SUM (Amount)
FOR Product IN
( [P1], [P2], [P3])
) AS pvt
我想在 Oracle 10g 中执行相同的操作.
I want to perform the same thing in Oracle 10g.
如何做到这一点?
这可能是微不足道的,但由于我对 Oracle 很陌生,所以我正在寻求帮助.
This may be trivial, but since i am very new to Oracle, so I am seeking for help.
谢谢
推荐答案
10G可以这样:
select salesboyname,
sum (case when product='P1' then amount end) as p1,
sum (case when product='P2' then amount end) as p2,
sum (case when product='P3' then amount end) as p3
from tblsales
group by salesboyname;
在 11G 中有一个类似于 SQL Server 的 PIVOT 关键字.
In 11G there is a PIVOT keyword similar to SQL Server's.
相关文章