如何在 Oracle 10g 中进行数据透视 [英] How to do Pivoting in 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.

这篇关于如何在 Oracle 10g 中进行数据透视的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！