问题：
您要在Oracle中PIVOT结果。
解
PIVOT子句是Oracle Database 11g的新增功能，它使您可以将行翻转为查询输出中的列，同时，还可以对数据运行聚合函数。
PIVOT对于查看大量数据的总体趋势特别有用。
我们将使用销售数据来演示用法。
示例SELECT * FROM sales;
输出结果12008 12 1998-02-28 6 37 552 18.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.
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.
06 116.25 8477308.28 13882822.54
07 157737.76 1145882.33 11410.4
08 167073.92 1214760.69 2010620.5
09 1274290.22 32017.99 15038327.33
10 160988.56 1180869.5 1956298.5
11 159736.4 11188 19635.2
12 1227572. 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
) ;
