深入SQL中PIVOT 行列轉換詳解，sqlpivot

深入SQL中PIVOT 行列轉換詳解，sqlpivot

PIVOT通過將運算式某一列中的唯一值轉換為輸出中的多個列來旋轉表值運算式，並在必要時對最終輸出中所需的任何其餘列值執行彙總。UNPIVOT與PIVOT執行相反的操作，將表值運算式的列轉換為列值。

通俗簡單的說：PIVOT就是行轉列，UNPIVOT就是列傳行

一、PIVOT執行個體

1. 建表

建立一個銷售情況表，其中，year欄位表示年份，quarter欄位表示季度，amount欄位表示銷售額。quarter欄位分別用Q1, Q2, Q3, Q4表示一、二、三、四季度。

 CREATE TABLE SalesByQuarter ( year INT, -- 年份  quarter CHAR(2), -- 季度  amount MONEY -- 總額 )

2. 填入表資料

使用如下程式填入表資料。

SET NOCOUNT ON DECLARE @index INT DECLARE @q INT SET @index = 0 DECLARE @year INT while (@index < 30) BEGIN  SET @year = 2005 + (@index % 4)  SET @q = (CAST((RAND() * 500) AS INT) % 4) + 1  INSERT INTO SalesByQuarter VALUES (@year, 'Q' + CAST(@q AS CHAR(1)), RAND() * 10000.00)  SET @index = @index + 1

3、如果我們要比較每年中各季度的銷售狀況，要怎麼辦呢？有以下兩種方法：

（1）、使用傳統Select的CASE語句查詢

在SQL Server以前的版本裡，將行級資料轉換為列級資料就要用到一系列CASE語句和彙總查詢。雖然這種方式讓開發人員具有了對所返回資料進行高度控制的能力，但是編寫出這些查詢是一件很麻煩的事情。

  SELECT year as 年份  , sum (case when quarter = 'Q1' then amount else 0 end) 一季度  , sum (case when quarter = 'Q2' then amount else 0 end) 二季度  , sum (case when quarter = 'Q3' then amount else 0 end) 三季度  , sum (case when quarter = 'Q4' then amount else 0 end) 四季度 FROM SalesByQuarter GROUP BY year ORDER BY year DESC

得到的結果如下：

（2）、使用PIVOT

由於SQL Server 2005有了新的PIVOT運算子，就不再需要CASE語句和GROUP BY語句了。（每個PIVOT查詢都涉及某種類型的彙總，因此你可以忽略GROUP BY語句。）PIVOT運算子讓我們能夠利用CASE語句查詢實現相同的功能，但是你可以用更少的代碼就實現，而且看起來更漂亮。

SELECT year as 年份, Q1 as 一季度, Q2 as 二季度, Q3 as 三季度, Q4 as 四季度 FROM SalesByQuarter PIVOT (SUM (amount) FOR quarter IN (Q1, Q2, Q3, Q4) ) AS P ORDER BY YEAR DESC

得到的結果如下：

二、通過下面一個執行個體詳細介紹PIVOT的過程

SELECT [星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日]--這裡是PIVOT第三步（選擇行轉列後的結果集的列）這裡可以用“*”表示選擇所有列，也可以只選擇某些列(也就是某些天)FROM WEEK_INCOME --這裡是PIVOT第二步驟(準備原始的查詢結果，因為PIVOT是對一個原始的查詢結果集進行轉換操作，所以先查詢一個結果集出來)這裡可以是一個select子查詢，但為子查詢時候要指定別名，否則語法錯誤PIVOT( SUM(INCOME) for [week] in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])--這裡是PIVOT第一步驟，也是核心的地方，進行行轉列操作。彙總函式SUM表示你需要怎樣處理轉換後的列的值，是總和(sum)，還是平均(avg)還是min,max等等。例如如果week_income表中有兩條資料並且其week都是“星期一”，其中一條的income是1000,另一條income是500，那麼在這裡使用sum，行轉列後“星期一”這個列的值當然是1500了。後面的for [week] in([星期一],[星期二]...)中 for [week]就是說將week列的值分別轉換成一個個列，也就是“以值變列”。但是需要轉換成列的值有可能有很多，我們只想取其中幾個值轉換成列，那麼怎樣取呢？就是在in裡面了，比如我此刻只想看工作日的收入，在in裡面就唯寫“星期一”至“星期五”（注意，in裡面是原來week列的值,"以值變列"）。總的來說，SUM(INCOME) for [week] in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])這句的意思如果直譯出來，就是說：將列[week]值為"星期一","星期二","星期三","星期四","星期五","星期六","星期日"分別轉換成列，這些列的值取income的總和。)TBL--別名一定要寫

三.UNPIVOT

很明顯，UN這個首碼表明了，它做的操作是跟PIVOT相反的，即列轉行。UNPIVOT操作涉及到以下三個邏輯處理階段。

1，產生副本
2，提取元素
3，刪除帶有NULL的行

UNPIVOT執行個體

CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int, Emp3 int, Emp4 int, Emp5 int);GOINSERT INTO pvt VALUES (1,4,3,5,4,4);INSERT INTO pvt VALUES (2,4,1,5,5,5);INSERT INTO pvt VALUES (3,4,3,5,4,4);INSERT INTO pvt VALUES (4,4,2,5,5,4);INSERT INTO pvt VALUES (5,5,1,5,5,5);GO--Unpivot the table.SELECT VendorID, Employee, OrdersFROM  (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5 FROM pvt) pUNPIVOT (Orders FOR Employee IN   (Emp1, Emp2, Emp3, Emp4, Emp5))AS unpvt;GO

上面UNPIVOT執行個體的分析

UNPIVOT的輸入是左表運算式P，第一步，先為P中的行產生多個副本，在UNPIVOT中出現的每一列，都會產生一個副本。因為這裡的IN子句有5個列名稱，所以要為每個來源行產生5個副本。結果得到的虛擬表中將新增一個列，用來以字串格式儲存來源列的名稱（for和IN之間的，上面例子是 Employee ）。第二步，根據新增的那一列中的值從來源列中提取出與列名對應的行。第三步，刪除掉結果列值為null的行，完成這個查詢。