Oracle 使用PIVOT語法

本篇介紹如何使用Oracle 11g才有的PIVOT查詢語法。

如果有經常使用Excel的人應該多少聽過或用過樞紐分析表(Pivot table)，是非常好用的資料摘要分析工具。

Pivot table的特色就是能行列互轉(rows to columns)，例如以下工作表為各餐點不同用餐時段銷售數量資料。

餐點名稱	用餐時段	銷售數量
滷肉飯	早餐	30
陽春麵	早餐	15
乾拌麵	早餐	18
荷包蛋	早餐	40
滷肉飯	午餐	50
陽春麵	午餐	35
乾拌麵	午餐	30
荷包蛋	午餐	84
滷肉飯	晚餐	60
陽春麵	晚餐	40
乾拌麵	晚餐	24
荷包蛋	晚餐	88

使用樞紐分析表可以把橫列的項目轉換為欄，例如下面把用餐時段轉為欄。

餐點名稱	早餐	午餐	晚餐
滷肉飯	30	50	60
陽春麵	15	30	40
乾拌麵	18	30	24
荷包蛋	40	84	88

下面則是餐點名稱轉為欄。

用餐時段	滷肉飯	陽春麵	乾拌麵	荷包蛋
早餐	30	15	18	40
午餐	50	35	30	84
晚餐	60	40	24	88

Oracle的PIVOT就提供類似的作用，語法如下。

SELECT * FROM table_name
PIVOT ( 
    pivot_clause
    pivot_for_clause
    pivot_in_clause
);

pivot_clause為要被聚合計算(aggregate)的欄位，通常搭配聚合函式(aggregate functions)如COUNT()、SUM()等；
pivot_for_clause為要被GROUP BY樞紐(pivot)的欄位，也就是由行變列的欄位；
pivot_in_clause用IN來篩選pivot_for_clause中樞紐的欄位。

例如建立一個餐點銷售數量表SALES_STATISTICS如下。

CREATE TABLE SALES_STATISTICS (
    ITEM VARCHAR2(30), -- 餐點名稱
    MEAL_TIME VARCHAR2(30), -- 用餐時段
    AMOUNT NUMBER -- 銷售數量
);

INSERT INTO SALES_STATISTICS VALUES ('braised-pork-rice', 'BREAKFAST', 30);
INSERT INTO SALES_STATISTICS VALUES ('soup-noodles', 'BREAKFAST', 15);
INSERT INTO SALES_STATISTICS VALUES ('stirred-noodles', 'BREAKFAST', 18);
INSERT INTO SALES_STATISTICS VALUES ('over-egg', 'BREAKFAST', 40);
INSERT INTO SALES_STATISTICS VALUES ('braised-pork-rice', 'LUNCH', 50);
INSERT INTO SALES_STATISTICS VALUES ('soup-noodles', 'LUNCH', 35);
INSERT INTO SALES_STATISTICS VALUES ('stirred-noodles', 'LUNCH', 30);
INSERT INTO SALES_STATISTICS VALUES ('over-egg', 'LUNCH', 84);
INSERT INTO SALES_STATISTICS VALUES ('braised-pork-rice', 'DINNER', 60);
INSERT INTO SALES_STATISTICS VALUES ('soup-noodles', 'DINNER', 40);
INSERT INTO SALES_STATISTICS VALUES ('stirred-noodles', 'DINNER', 24);
INSERT INTO SALES_STATISTICS VALUES ('over-egg', 'DINNER', 88);

+-------------------+-----------+--------+
|       ITEM        | MEAL_TIME | AMOUNT |
+-------------------+-----------+--------+
| braised-pork-rice | BREAKFAST |     30 |
| soup-noodles      | BREAKFAST |     15 |
| stirred-noodles   | BREAKFAST |     18 |
| over-egg          | BREAKFAST |     40 |
| braised-pork-rice | LUNCH     |     50 |
| soup-noodles      | LUNCH     |     35 |
| stirred-noodles   | LUNCH     |     30 |
| over-egg          | LUNCH     |     84 |
| braised-pork-rice | DINNER    |     60 |
| soup-noodles      | DINNER    |     40 |
| stirred-noodles   | DINNER    |     24 |
| over-egg          | DINNER    |     88 |
+-------------------+-----------+--------+

使用PIVOT查詢把用餐時段(MEAL_TIME)轉為欄。

SELECT * FROM SALES_STATISTICS
    PIVOT (
        SUM (AMOUNT)
        FOR MEAL_TIME 
        IN ('BREAKFAST' , 'LUNCH', 'DINNER')
    );

+-------------------+-------------+---------+----------+
|       ITEM        | 'BREAKFAST' | 'LUNCH' | 'DINNER' |
+-------------------+-------------+---------+----------+
| braised-pork-rice |          30 |      50 |       60 |
| soup-noodles      |          15 |      35 |       40 |
| stirred-noodles   |          18 |      30 |       24 |
| over-egg          |          40 |      84 |       88 |
+-------------------+-------------+---------+----------+

使用PIVOT查詢把餐點名稱(ITEM)轉為欄。

SELECT * FROM SALES_STATISTICS
    PIVOT (
        SUM (AMOUNT)
        FOR ITEM 
        IN ('braised-pork-rice' , 'soup-noodles', 'stirred-noodles', 'over-egg')
    );

+-----------+---------------------+----------------+-------------------+------------+
| MEAL_TIME | 'braised-pork-rice' | 'soup-noodles' | 'stirred-noodles' | 'over-egg' |
+-----------+---------------------+----------------+-------------------+------------+
| LUNCH     |                  50 |             35 |                30 |         84 |
| BREAKFAST |                  30 |             15 |                18 |         40 |
| DINNER    |                  60 |             40 |                24 |         88 |
+-----------+---------------------+----------------+-------------------+------------+

若本篇有幫助到您還幫忙點個Google廣告，感恩。

• pivot_clause
• Using PIVOT and UNPIVOT: Examples
• Oracle / PLSQL: PIVOT Clause