先前文章《資料轉置(PIVOT-Row2Col)》曾探討資料轉置(Row to Column)之概念，及如何使用基本指令達成資料轉置目的，MSSQL2005或ORACLE 11g（含）以上的版本，提供資料轉置語法PIVOT可直接將資料列轉置為資料欄(Row to Column)的顯示方式，以下分別以銷售日期及客戶兩種轉置方式來說明PIVOT之用法（以下是MSSQL的PIVOT語法）。
MSSQL
|
ORACLE
|
SELECT …
|
SELECT …
|
FROM <Table
Source> AS <Tab Src Alias>
|
FROM <Table Source>
|
PIVOT
|
PIVOT
|
(
|
(
|
<agg func>(<Val col>)
|
<agg func>(<Val col>) [AS <Alias>]
|
[,<agg func>(<Val
col >) [AS
<Alias>]]
|
|
FOR <Pivot Column>
|
FOR <Pivot Column>
|
IN (
|
IN (
|
<Column
List>
|
<Value1>
AS <Column Alias>
|
, <Value2>
AS <Column Alias>
|
|
, …
|
|
, <ValueN>
AS <Column Alias>
|
|
)
|
)
|
) AS <Pivot Table Alias>
|
)
|
WHERE
|
WHERE
|
引數
|
說明
|
MSSQL
|
ORACLE
|
Table Source
|
需要進行Pivot的資料來源
(資料表、子查詢或CT)
|
||
Tab Src Alias
|
資料來源別名
|
必要
|
選擇性
(不可搭配AS)
|
aggregation function
(Value column)
|
針對指定欄位執行彙總函數運算，如SUM、COUNT、MAX…等
|
限1組
指定欄位
|
多組
可為運算(Expr)
|
彙總計算別名
|
不可使用
|
選擇性
使用時則會於轉置欄位自動加上。
|
|
Pivot Column
|
用以轉置成欄位名稱之原始資料列欄位
|
||
Column List
|
列出Pivot Column中，將呈現於輸出資料表之資料欄位名稱的值
|
欄位名稱，使用雙引號(")或中括號([
])括住欄位名稱，不可搭配別名。
|
資料值，使用單引號(')括住欄位名稱，但可搭配別名。
|
Pivot Tab
Alias
|
樞紐資料表的別名
|
以訂單日期為轉置欄位
SELECT Cust_Name
, "2009-08-01"
,
"2009-08-02"
,
"2009-08-03"
FROM
(
SELECT CONVERT(CHAR(10),Order_Date, 120) Order_Date
--FORMAT(Order_Date,
'yyyy-MM-dd') Order_Date
, Cust_Name
--, Qty --必需註解掉，否則結果會乘開
--, Price --必需註解掉，否則結果會乘開
, Qty * Price AR
FROM Orders
WHERE 1=1
AND Order_Date BETWEEN '2009-08-01' AND '2009-08-04'
)AS D
PIVOT
(
SUM(AR)
--SUM(QTY*PRICE)
AS AR --發生Incorrect
syntax near '*'. 錯誤訊息
FOR Order_Date
IN ([2009-08-01], [2009-08-02],
"2009-08-03")
) AS P
ORDER BY Cust_Name COLLATE
chinese_taiwan_stroke_cs_as_ks_ws --中文筆劃排序
|
Cust_Name
|
2009-08-01
|
2009-08-02
|
2009-08-03
|
|
1
|
李先生
|
9980
|
13900
|
13140
|
2
|
張先生
|
5040
|
20340
|
|
3
|
曹先生
|
8200
|
7540
|
|
4
|
陳先生
|
16200
|
MSSQL的PIVOT功能有以下幾個重點：
l
彙總函數數量限制：只能指定一組彙總函數，以上述範例為SUM(AR)，若欲需增加計算總數如SUM(Qty)或COUNT(*)則需改用其他方式，另外，不可使用別名(Alias)。
l
彙總函數運算式限制：上述範例是加總應收帳款（AR），若直接採用原計算公式即SUM(Qty*Price)，則將發生錯誤，運算需在資料來源(Table Source)段先行處理，且除彙總群組、轉置欄位、彙總計算等必要欄位外，其餘欄位均不可出現(彙總運算概念)。
l 轉置資料原始欄位（即參數之Pivot Column）：不可在Pivot Column執行任何運算，以下語法以銷售日期為例，不可以對其做轉換，需在進行樞紐分析之資料來源做轉換。就合理性來判斷的確為正確做法，但對許多剛使用此指令的使用者會有誤解，這點並不限於MSSQL，在ORACLE也相同。
FOR CONVERT(CHAR(10),OrderDate, 120)
IN ([2009-08-01], [2009-08-02],
"2009-08-03")
|
l 轉置資料值（即參數之Column List）：必需用中括號（[ ]）或雙引號（"）括住欄位名稱，不管是文字、日期或數字型態均需適用，如下所示：
FOR Order_Date
IN ([2009-08-01],
[2009-08-02], "2009-08-03")
l 別名（Alias）：資料來源若為子查詢，則需指定別名。MSSQL子查詢都需指定別名，並不限於PIVOT功能，因ORACLE對子查詢並未要求指定別名，對原本為ORACLE而轉換至MSSQL的使用者或者需做系統移轉（Migration）則需特別注意。
11g（含）以上開始提供PIVOT函數，將前述MSSQL的SQL改以ORACLE語法重新撰寫如下：
SELECT *
FROM
(
SELECT
TO_CHAR(Order_Date, 'YYYY-MM-DD')
Order_Date
, Cust_Name
, Qty * Price AR --測試1
--, Qty --測試2
--, Price --測試3
FROM ORDERS
WHERE 1=1
AND
Order_Date BETWEEN date'2009-08-01' AND date'2009-08-04'
) D
PIVOT
(
SUM(AR) --AS AR
--SUM(QTY*PRICE)
FOR
Order_Date
IN ('2009-08-01' , '2009-08-02'
"2019-08-02", '2009-08-03'
"08-03")
) P
ORDER BY nlssort(Cust_Name,'NLS_SORT=TCHINESE_STROKE_M') --中文筆劃排序
|
CUST_NAME
|
'2009-08-01'
|
2009-08-02
|
08-03
|
|
1
|
李先生
|
9980
|
13900
|
13140
|
2
|
張先生
|
5040
|
20340
|
|
3
|
曹先生
|
8200
|
7540
|
|
4
|
陳先生
|
16200
|
參數說明請直接參考MSSQL ，以下將列出ORALCE的PIVOT語法中額外說明的幾項特點：
l
彙總函數：ORACLE允許多組的彙總函數(MSSQL僅限一組)，可使用單一欄位(如測試1)或者欄位運算(Expression, 如測試2)，但兩者不可混用。
l 別名的使用：可使用，但不可搭配AS關鍵字
先前文章曾介紹資料來源（如資料表）以簡捷且有意義的別名代表，別名可使SQL命令變得比較簡短並可增加可讀性，別名可直接置於資料來源後方或使用AS關鍵字明確表示，此兩種別名型式通常均可應用，但PIVOT中的資料來源部分卻發生【ORA-00933:SQL命令結束有問題】的錯誤，在前述語法中，在資料來源別名的AS關鍵字上加上刪除線，代表不可使用AS關鍵字。
另外，MSSQL的彙總運算欄位是不可使用別名（Alias），但對於ORACLE則視彙總運算組數而定，一組時為選選性，但多組時為區分結果欄位則必需使用，否則將發生『ORA-00918: column
ambiguously defined』錯誤。
彙總函數
|
ORACLE
|
MSSQL
|
1組
|
支援
別名(可選擇)
|
支援
|
多組
|
支援
別名(必需)
|
不支援
|
SQL標示灰底部分是與MSSQL最大的差異，下表列出兩種資料庫的語法比較。MSSQL視In-List中的資料為欄位，In-List中的[2009-08-02]，"2009-08-03"兩種格式均屬於欄位名稱型式；ORACLE則視為資料值，如In-List中的'2009-08-01'為字串資料，此處的操作如同一般SQL語法將資料值採用欄位別名的方式轉換。
資料庫
|
SQL 命令
|
MSSQL
|
FOR OrderDate
IN ([2009-08-01], [2009-08-02],
"2009-08-03")
|
ORACLE
|
FOR OrderDate
IN ('2009-08-01'
, '200908-02'
"2009-08-02"
, '2009-08-03'
"08-03"
)
|
先前所介紹PIVOT指令應用均以一個彙總函數為範例，實務上，經常需將多種彙總資料置於同一表格中，ORACLE的PIVOT指令提供多欄位（Multiple Columns）的操作，以下範例除計算每個客戶應收帳款（AR）外，並額外顯示訂單筆數，SQL語法及結果如下所示：
SELECT *
FROM
(
SELECT
TO_CHAR(Order_Date, 'YYYY-MM-DD') OrderDate
, Cust_Name
, Qty
, Price
FROM
Orders
WHERE
1=1
AND Order_Date BETWEEN
date'2009-08-01' AND
date'2009-08-04'
)
PIVOT
(
SUM(Qty *
Price) AS
"AR"
, COUNT(*) AS
"Qty" -- 訂單筆量(多運算欄位)
FOR OrderDate
IN ('2009-08-01' AS
"08-01", '2009-08-02'
"08-02", '2009-08-03'
"08-03")
)
P
ORDER BY nlssort(Cust_Name,'NLS_SORT=TCHINESE_STROKE_M') --中文筆劃排序
|
CUST_NAME
|
08-01_AR
|
08-01_Qty
|
08-02_AR
|
08-02_Qty
|
08-03_AR
|
08-03_Qty
|
|
1
|
李先生
|
9980
|
2
|
13900
|
2
|
13140
|
2
|
2
|
張先生
|
5040
|
1
|
0
|
20340
|
3
|
|
3
|
曹先生
|
0
|
8200
|
2
|
7540
|
1
|
|
4
|
陳先生
|
16200
|
2
|
0
|
0
|
根據前述SQL語法及結果將重點整理如下：
l
多運算欄位：
在結果中可同時顯示應用帳款（AR）及訂單筆數（Orders）。
l
欄位名稱命名：
在單一運算欄位時，若彙總運算並未指定別名時，系統會自系統會以欄位值別名直接命名，因每一種運算將按欄位值別名產生一組欄位名稱，當多組彙總運算時則發生欄位名稱重覆的問題（ORA-00918），為避免此一問題則需針對每一組彙總運算賦予其彙總運算別名，ORACLE會自動將彙總運算別名與欄位值別名串聯，並以底線（_）符號會分隔字元，以產生唯一的欄位名稱。可由上表中08-01_AR欄位是以欄位值別名"08-01"及"AR"彙總運算別名組合而成。當然單一彙總運算欄位也可對彙總運算指定其彙總運算別名。
由上其實可發現，使用PIVOT進行Row To Column轉置時，此指令雖相當簡便，但有些許限制，如MSSQL不支援多運算欄位，或者自動命名規則等，可參考另一篇《資料轉置(PIVOT-Row2Col)》以基本函數達成的轉置需求。為比較兩種資料庫資料上方便，因此使用中文筆劃排序法，詳細說明及使用可參考其他文章所述。
沒有留言:
張貼留言