前言

在 SQL Server 2005 Transact-SQL陳述式上，提供了「PIVOT 關係運算子」與「UNPIVOT 關係運算子」。可以用來將資料表值運算式變更為另一個資料表。

其中，PIVOT 會將運算式內一個資料行中的唯一值轉成輸出中的多個資料行，以旋轉資料表值運算式，然後依據最終輸出的需要，對其餘的任何資料行值執行必要的彙總。而UNPIVOT 執行的作業則與 PIVOT 相反，它會將資料表值運算式旋轉為資料行值。

在SSIS 2005上的「資料轉換工作」內，提供了「樞紐轉換(Pivot transformation)」與「取消樞紐轉換(Unpivot transformation)」來達成所需要的功能。

樞紐轉換的補充事項

但在SQL Server 2008 R2 線上叢書，並沒有特別說明幾個注意事項，筆者整理如下：

• 在「樞紐」轉換上，沒有提供彙總運算的功能，請事先使用「彙總」轉換來處理彙總運算。
• 在「樞紐」轉換上，擔任「Pivot Key」的資料行，其資料是不得有重複值，而且需要已經排序好的。

因此，請檢視其 SortKeyPosition 屬性的設定，並事先利用 ORDER BY 子句或是「排序」轉換執行運算處理。也就是說，若要使用「樞紐」轉換，請先使用「彙總」轉換，使用「排序」轉換後，才能使用「樞紐」轉換。

示範環境

SQL Server 2008 R2 x64版本。

實作練習一：認識樞紐轉換(Pivot transformation)

工作目標：

• 使用資料流程工作，設定資料流程來源
• 使用彙總轉換
• 使用排序轉換
• 使用樞紐轉換
• 使用資料流程目的地
• 執行封裝

工作一：使用資料流程工作，設定資料流程來源

步驟01. 檢視資料來源：myOrders01.xlsx

圖1：Excel2010_資料來源

步驟01. 在「封裝設計師」視窗，新增加一個「資料流程」工作，命名為：樞紐轉換_Pivot。

步驟02. 點選進入到「資料流程」頁面，在左邊的工具箱，由「資料流程來源」區域，拖曳所需的來源物件，設定與其連線的資訊連線。

在本次範例中，使用一個「OLE DB 來源」，連接到 Excel 2010 (*.xlsx) 為例：myOrders_Excel_2010。

圖2：增加_資料來源

工作二：使用彙總轉換

步驟01. 在左邊的工具箱，由「資料流程轉換」區域，選擇「彙總」轉換拖曳到右邊的封裝設計師頁面上，命名為：彙總轉換_SubTotal。

步驟02. 點選資料來源：myOrders_Excel_2010，將其綠色線路的「資料流程路徑」，拖曳到「彙總」轉換上。

步驟03. 滑鼠雙擊此「彙總」轉換，在「彙總轉換編輯器」視窗，設定以下參數：

• 在「可用的輸入資料行」區域，勾選所需的資料行，例如：Customer、Product、Quantity。
• 設定 Customer 的「作業」為：群組依據。
• 設定 Product 的「作業」為：群組依據。
• 設定 Quantity 的「作業」為：總和，並修改其「輸出別名」為：_SubTotal。

圖3：設定「彙總轉換編輯器」

步驟04. 在「彙總轉換編輯器」視窗，點選「確定」。

圖4：檢視已經加入彙總轉換

工作三：使用排序轉換

步驟01. 在左邊的工具箱，由「資料流程轉換」區域，選擇「排序」轉換拖曳到右邊的封裝設計師頁面上，命名為：排序轉換_Customer。

步驟02. 點選「彙總」轉換：彙總轉換_SubTotal，將其綠色線路的「資料流程路徑」，拖曳到「排序」轉換：排序轉換_Customer 上。

步驟03. 滑鼠雙擊此「排序」轉換，在「排序轉換編輯器」視窗，設定以下參數：

在「可用的輸入資料行」區域，勾選所需的資料行，例如：Customer。

圖5：設定「排序轉換編輯器」

步驟04. 在「排序轉換編輯器」視窗，點選「確定」。

圖6：檢視已經加入排序轉換

工作四：使用樞紐轉換

步驟01. 在左邊的工具箱，由「資料流程轉換」區域，選擇「樞紐」轉換拖曳到右邊的封裝設計師頁面上。

步驟02. 點選「排序」轉換：排序轉換_Customer，將其綠色線路的「資料流程路徑」，拖曳到「樞紐」轉換：樞紐轉換_Product 上。

步驟03. 滑鼠雙擊此「樞紐」轉換，在「樞紐 的進階編輯器」視窗，設定以下參數：

• 點選「元件屬性」頁籤，在「Name」方塊，輸入：樞紐轉換_Product。

圖7：設定「元件屬性」，在「樞紐 的進階編輯器」視窗

• 點選「輸入資料行」頁籤，在「可用的輸入資料行」區域，勾選所需要的資料行，例如：Customer、Product、SubTotal。

圖8：尚未勾選_在「可用的輸入資料行」區域

圖9：已經勾選_在「可用的輸入資料行」區域

• 在「使用類型」區域，下拉選取可以看到有以下的類型：READONLY 與 READWRITE。

圖10：可以使用類型_READONLY 與 READWRITE

• 點選下方的「重新整理」。

圖11：點選下方的「重新整理」

在上圖，點選「重新整理」後，關於「輸入資料行」的檢查錯誤之警告訊息應該會消失。

步驟04. 延續步驟03. ，繼續點選「輸入與輸出屬性」頁籤，設定以下參數：

圖12：展開「輸入與輸出屬性」頁籤

• 在「輸入及輸出」區域，展開「樞紐預設輸入」\「輸入資料行」節點，可以觀察到先在「輸入資料行」頁籤上所勾選的資料行，例如：Customer、Product、SubTotal。
• 設定「PivotUsage」屬性：
• 點選「Customer」方塊，在右邊窗格，在「自定屬性」區域，點選「PivotUsage」，輸入：1。
  

圖13：Customer_「PivotUsage」屬性，輸入：1

• 點選「Product」方塊，在右邊窗格，在「自定屬性」區域，點選「PivotUsage」，輸入：2。
  

圖14：Product_「PivotUsage」屬性，輸入：2

• 點選「Quantity」方塊，在右邊窗格，在「自定屬性」區域，點選「PivotUsage」，輸入：3。
  

圖15：SubTotal_「PivotUsage」屬性，輸入：3

步驟05. 延續步驟04.，繼續點選「輸入與輸出屬性」頁籤，設定以下參數：

• 在「輸入及輸出」區域，展開「樞紐預設輸出」」\「輸出資料行」節點，增加以下的「輸出資料行」，例如：客戶、Bike、Chain。
• 其中，設定輸出資料行：「客戶」，應該對應到「輸入資料行」：Customer。因此，請先找出此資料行的歷程識別碼「LineageID」屬性，例如：84。請參考上圖：13_Customer_「PivotUsage」屬性，輸入：1。
• 點選「加入資料行」，在右邊窗格，設定以下的參數：
• 在「Name」方塊，輸入：客戶。
• 在「SourceColumn」方塊，輸入：84。
  

圖16：設定客戶的「SourceColumn」方塊

• 接下來，設定輸出資料行：Bike，應該對應到「輸入資料行」：SubTotal。因此，請先找出此資料行的歷程識別碼「LineageID」屬性，例如：94。請參考上圖：15_SubTotal_「PivotUsage」屬性，輸入：3。
• 點選「加入資料行」，在右邊窗格，設定以下的參數：
• 在「Name」方塊，輸入：Bike。
• 在「PivotKeyValue」方塊，輸入：Bike。
• 在「SourceColumn」方塊，輸入：94。

圖17：設定 Bike 的「PivotKeyValue」與「SourceColumn」方塊

• 接下來，設定輸出資料行：Chain，應該對應到「輸入資料行」：SubTotal。因此，請先找出此資料行的歷程識別碼「LineageID」屬性，例如：94。
• 點選「加入資料行」，在右邊窗格，設定以下的參數：
• 在「Name」方塊，輸入：Chain。
• 在「PivotKeyValue」方塊，輸入：Chain。
• 在「SourceColumn」方塊，輸入：94。

圖18：設定 Chain 的「PivotKeyValue」與「SourceColumn」方塊

步驟06. 點選「確定」。

由上，已經完成設定輸出資料行：客戶、Bike、Chain。

圖19：檢視已經設定「樞紐轉換 」

工作五：使用資料流程目的地

步驟01. 在左邊的工具箱，由「資料流程目的地」區域，選擇「一般檔案目的地」物件，拖曳到右邊的封裝設計師頁面上，命名為：一般檔案目的地_myPivot。

步驟02. 點選資料轉換：合併聯結_內部的綠色線路之「資料流程路徑」，拖曳到「一般檔案目的地」物件。

步驟03. 滑鼠雙擊此「一般檔案目的地」物件，設定相關的連線資料。

圖20：檢視已經設定好_一般檔案目的地_myPivot

工作六. 執行封裝

步驟01. 執行此封裝。

圖21：執行封裝

步驟02. 停止與結束執行，在每個「資料流程路徑」上，加入「資料檢視器」，再度執行此封裝。

圖22：檢視來源資料_使用「資料檢視器」

圖23：檢視彙總轉換後_使用「資料檢視器」

圖24：檢視排序轉換後_使用「資料檢視器」

圖25：檢視樞紐轉換後_使用「資料檢視器」

圖26：檢視一般檔案目的地

認識樞紐選項

在先前的步驟中，我們設定PivotUsage 屬性，此屬性是以指定每個資料行在樞紐處理中執行的角色。

PivotUsage 的有效值為 0、1、2 和 3。請參考以下表格1的說明：

選項	描述
0	資料行未經樞紐，且資料行值已傳送至轉換輸出。
1	資料行是集索引鍵的一部分，可識別一個集中的一或多個資料列。 具有相同集索引鍵的所有輸入資料列會組合成一個輸出資料列。
2	資料行為樞紐資料行。至少應從每個資料行值建立一個資料行。
3	此資料行的值會置於樞紐後建立的資料行中。

表1：PivotUsage 選項

注意事項：

在設定「PivotUsage」屬性時，至少有一個資料行設定為 2，至少一個資料行設定為 3。而設定為 0 或 1 值部分，則是可以選擇一個輸入資料行擔任。

以下的表格2，描述樞紐轉換之輸出資料行的自訂屬性，其所有屬性都是可讀寫的。

屬性	資料類型	描述
PivotKeyValue	字串	資料行的其中一個可��值，此值會由其 PivotUsage 屬性的值標示為樞紐索引鍵。 此屬性的值可以使用屬性運算式指定。
SourceColumn	整數	包含樞紐值之輸入資料行的 LineageID，或 -1。值為 -1 表示此資料行不會用於樞紐作業中。

表2：樞紐轉換之輸出資料行的自訂屬性

---

實作練習二：認識取消樞紐轉換(Unpivot transformation)

工作目標：

• 使用資料流程工作，設定資料流程來源
• 使用取消樞紐轉換
• 使用資料流程目的地
• 執行封裝

工作一：使用資料流程工作，設定資料流程來源

步驟01. 檢視資料來源：myPivot.txt

圖27：myPivot_資料來源

步驟02. 在「封裝設計師」視窗，新增加一個「資料流程」工作，命名為：取消樞紐轉換_UnPivot。

步驟03. 點選進入到「資料流程」頁面，在左邊的工具箱，由「資料流程來源」區域，拖曳所需的來源物件，設定與其連線的資訊連線。

• 在本範例中，使用一個「一般檔案來源」，連接到文字檔案為例：myPivot.txt，命名為：一般檔案來源_myPivot。
  

圖28：myPivot_資料來源

工作二：使用取消樞紐轉換

步驟01. 在左邊的工具箱，由「資料流程轉換」區域，選擇「取消樞紐」轉換拖曳到右邊的封裝設計師頁面上，命名為：取消樞紐轉換_數量。

步驟02. 點選資料來源：一般檔案來源_myPivot，將其綠色線路的「資料流程路徑」，拖曳到「取消樞紐」轉換：取消樞紐轉換_數量 上。

步驟03. 滑鼠雙擊此「取消樞紐」轉換，在「取消樞紐轉換編輯器」視窗，設定以下參數：

圖29：初始化的「取消樞紐轉換編輯器」

• 在「可用的輸入資料行」區域，勾選：Bike 與 Chain。
• 客戶部分，仍是設定為「通過」。
• 在下方區域，對「輸入資料行」：Bike 與 Chain 的「目的資料行」，輸入：數量。
• 而其「樞紐索引鍵值(Pivot Key Value)」區域，仍是使用其預設產生的值：Bike 與 Chain。
• 在「樞紐索引鍵值資料行名稱」方塊，輸入：產品。

圖30：設定「取消樞紐轉換編輯器」

步驟04. 在「取消樞紐轉換編輯器」視窗，點選「確定」。

圖31：檢視_已經加入_取消樞紐轉換

工作三：使用資料流程目的地

步驟01. 在左邊的工具箱，由「資料流程目的地」區域，選擇「一般檔案目的地」物件，拖曳到右邊的封裝設計師頁面上，命名為：一般檔案目的地_myUnPivot。

步驟02. 點選「取消樞紐」轉換：取消樞紐轉換_數量的綠色線路之「資料流程路徑」，拖曳到「一般檔案目的地」物件。

步驟03. 滑鼠雙擊此「一般檔案目的地」物件，設定相關的連線資料。

圖32：檢視_已經設定好_一般檔案目的地_myUnPivot

工作四：執行封裝

步驟01. 執行此封裝。

圖33：執行封裝

步驟02. 停止與結束執行，在每個「資料流程路徑」上，加入「資料檢視器」，再度執行此封裝。

圖34：檢視_資料來源_使用「資料檢視器」

圖35：取消樞紐轉換_使用「資料檢視器」

圖36：檢視_一般檔案目的地

結語

在本期中，介紹了使用SSIS 2008 的「樞紐轉換(Pivot transformation)」與「取消樞紐轉換(Unpivot transformation)」來執行將資料表值運算式變更為另一個資料表。