如何在 Oracle 中 PIVOT 结果？

问题

你希望在 Oracle 中 PIVOT 结果。

解决方案

PIVOT 子句是 Oracle Database 11g 的一项新功能，它能够让你将查询结果中的行翻转为列，并同时允许你在数据上运行一个聚合函数。

PIVOT 对于查看大量数据中的整体趋势特别有用。

我们将使用销售数据来演示用法。

示例

SELECT * FROM sales;

输出

12008   12  1998-02-28  6   37  552 1898.88
12008   18  1998-02-28  6   37  463 1592.72
12008   20  1998-02-28  6   37  2430    8359.2
12008   25  1998-03-31  6   36  3387    12091.59
12008   26  1998-01-31  6   37  2381    8190.64

PIVOT 的语法。

示例

SELECT *
FROM (
  inner_query
)
PIVOT (
  aggregate_function FOR pivot_column IN (list_of_values)
)
ORDER BY…;

我们将使用 PIVOT 来查看一段时间内不同类型产品在销售中的趋势。以下是类似的示例。

示例

SELECT *
FROM
  (SELECT SUBSTR(time_id,06,02) AS month_val,
    prod_id,
    amount_sold
  FROM sales
  WHERE SUBSTR(time_id,01,04)           = 1998
  ) PIVOT ( SUM(amount_sold) FOR prod_id IN (12008 , 12010 , 12011 ) )
ORDER BY month_val;

输出

01  145714.96   1039519.04  1719280.2
02  149309.76   1067082.24  1758706.44
03  1072859.97  8045916.84  13191209.28
04  154280.56   1106052.23  1826847.36
05  155322.88   1119728.52  1848467.64
06  1166464.25  8477308.28  13882822.54
07  157737.76   1145882.33  1891410.4
08  167073.92   1214760.69  2010620.5
09  1274290.22  8932017.99  15038327.33
10  160988.56   1180869.5   1956298.5
11  159736.4    1189188     1964635.2
12  1227572.64  8795186.2   14774142.24

 我们还可以将多个表添加到 PIVOT 中。这里有一个示例。

示例

SELECT *
FROM
  (SELECT SUBSTR(time_id,06,02) AS month_val,
    prod_id,
    amount_sold
  FROM sales
  WHERE SUBSTR(time_id,01,04) = 1998
  ) PIVOT ( SUM(amount_sold) FOR (month_val, prod_id) IN ((01,12008), (02, 12010) ,(03, 12011 ) ));

此外，我们还可以在 PIVOT 查询中使用多个聚合函数。

示例

SELECT *
FROM
  (SELECT SUBSTR(time_id,06,02) AS month_val,
    prod_id,
    amount_sold
  FROM sales
  WHERE SUBSTR(time_id,01,04)= 1998
  ) PIVOT ( SUM(amount_sold),AVG(amount_sold) AS avg_amount FOR (month_val, prod_id) IN ((01,12008), (02, 12010) ,(03, 12011 ) ));

针对以上问题陈述的表结构:

示例

  CREATE TABLE "SALES"
   (    "PROD_ID" NUMBER NOT NULL ENABLE,
    "CUST_ID" NUMBER NOT NULL ENABLE,
    "TIME_ID" VARCHAR2(20) NOT NULL ENABLE,
    "CHANNEL_ID" NUMBER NOT NULL ENABLE,
    "PROMO_ID" NUMBER NOT NULL ENABLE,
    "QUANTITY_SOLD" NUMBER(10,2) NOT NULL ENABLE,
    "AMOUNT_SOLD" NUMBER(10,2) NOT NULL ENABLE
   ) ;

更新日期： 2020 年 12 月 5 日

1.4 万多次查看

开启您的 职业生涯

完成课程，获得认证

开始
广告