關於SQLServer 中行列互轉的執行個體說明

標籤：

這幾天在做一個招標系統中審批模組，其中關於報價資訊這塊，用到了pivot和unpivot來實現資料的行列互轉，下面簡單介紹一下，實際案例，便於回憶和記錄相關的條件下使用的情況。pivot 與 unpivot 函數是SQL2005新提供的2個函數，PIVOT 通過將運算式某一列中的唯一值轉換為輸出中的多個列來旋轉表值運算式，並在必要時對最終輸出中所需的任何其餘列值執行彙總。UNPIVOT 與 PIVOT 執行相反的操作，將表值運算式的列轉換為列值。

      下面我通過PIVOT 來闡述整個函數的使用：

      文法：

SELECT <非透視的列>,

    [第一個透視的列] AS <列名稱>,

     [第二個透視的列] AS <列名稱>, ...

[最後一個透視的列] AS <列名稱>,

FROM(<產生資料的 SELECT 查詢>)

 AS <源查詢的別名>

PIVOT(

<彙總函式>(<要彙總的列>)

FOR

[<包含要成為欄位標題的值的列>]

IN ( [第一個透視的列], [第二個透視的列], ... [最後一個透視的列])

) AS <透視表的別名>

執行個體：
select PRICE,Sup_Name,QUOT_ITEM1,QUOT_ITEM3,QUANTITY from
 Q2B_QUOT_ITEM where <相關條件式篩選>

執行顯示：

       PRICE Sup_Name QUOT_ITEM1   QUOT_ITEM3 QUANTITY
         342 測試供應商1 5H52921000088 剪刀              4.000
         422 測試供應商1 5H48911000023 黑色水筆芯     2.000
         211 測試供應商1 5H57161000002 計算機           2.000
         324 測試供應商2 5H52921000088 剪刀              4.000
         342 測試供應商2 5H48911000023 黑色水筆芯      2.000
         234 測試供應商2 5H57161000002 計算機           2.000
         434 測試供應商3 5H52921000088 剪刀              4.000
         232 測試供應商3 5H48911000023 黑色水筆芯     2.000
        2432 測試供應商3 5H57161000002 計算機          2.000

發現正常情況下讀取資料顯示的是按照Sup_Name（供應商)作為列值顯示。目前客戶要求以物資為條件對各供應上報價進行匯總顯示。接下來我們按照剛才提供的文法使用pivot來實現列轉行。

  sql：select* from (select PRICE,Sup_Name,QUOT_ITEM1,QUOT_ITEM3,QUANTITY from
Q2B_QUOT_ITEM where <相關條件式篩選> as <別名>

pivot (max(PRICE) for ord.Sup_Name in (測試供應商1,測試供應商3,測試供應商2)) b

顯示：QUOT_ITEM1 QUOT_ITEM3 QUANTITY 測試供應商1 測試供應商3 測試供應商2
      5H48911000023 黑色水筆芯     2.000          422          232          342
      5H52921000088 剪刀              4.000          342          434          324
       5H57161000002 計算機          2.000          211         2432          234

 使用pivot很簡單的實現了列轉行，對於類似的資料處理灰常灰常的實用，避免了使用case when 或者迴圈遊標的複雜處理，大大提高了處理速度和代碼整潔優雅。

注意事項： 1.對升級到 SQL Server 2005 或更高版本的資料庫使用 PIVOT 和 UNPIVOT 時，必須將資料庫的相容層級設定為 90 或更高;

                2.UNPIVOT 將與 PIVOT 執行幾乎完全相反的操作，將列轉換為行,但是也不是完全的相同，PIVOT 會執行一次彙總，從而將多個可能的行合并為輸出中的單個行。而 UNPIVOT 不會重現原始表值運算式的結果，因為行已經被合并了。另外，UNPIVOT 的輸入中的空值不會顯示在輸出中，而在執行 PIVOT 操作之前，輸入中可能有原始的空值。

               3.動態處理和靜態處理不一樣的地方在於列轉行的數量。也就是FOR ... in 包含的資料。

 最後簡單寫一下：UNPIVOT用於將列名轉為列值（即列轉行）

文法：

UNPIVOT(

value_column

FOR  pivot_column

IN(<column_list>)

)

簡單測試了一下，待以後有實際應用再把實際例子複製過來。不繼續深入探討了。

 

關於SQLServer 中行列互轉的執行個體說明