如何在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
   ) ;