行轉列：SQL SERVER PIVOT與用法解釋

標籤：blog   http   使用   os   io   資料   for   html   

轉自：http://www.cnblogs.com/lwhkdash/archive/2012/06/26/2562979.html

在資料庫操作中，有些時候我們遇到需要實現“行轉列”的需求，例如一下的表為某店鋪的一周收入情況表：

WEEK_INCOME(WEEK VARCHAR(10),INCOME DECIMAL)

我們先插入一些類比資料：

INSERT INTO WEEK_INCOME SELECT ‘星期一‘,1000UNION ALLSELECT ‘星期二‘,2000UNION ALLSELECT ‘星期三‘,3000UNION ALLSELECT ‘星期四‘,4000UNION ALLSELECT ‘星期五‘,5000UNION ALLSELECT ‘星期六‘,6000UNION ALLSELECT ‘星期日‘,7000

 

一般我們最經常使用的查詢是查詢一周中每天或某幾天的收入，例如查詢周一至周日全部的收入：

SELECT WEEK,INCOME FROM WEEK_INCOME

得到如下的查詢結果集：

WEEK           INCOME
星期一           1000
星期二           2000
星期三           3000
星期四           4000
星期五           5000
星期六           6000
星期日           7000

 

但是在一些情況下（往往是某些報表中），我們希望在一行中顯示周一至周日的收入，這時候查詢結果集應該是這樣的：

星期一   星期二   星期三   星期四   星期五   星期六   星期日
1000     2000     3000     4000     5000     6000     7000

這種情況下，SQL查詢語句可以這樣寫：

SELECT  SUM(CASE WEEK WHEN ‘星期一‘ THEN INCOME END) AS [星期一],SUM(CASE WEEK WHEN ‘星期二‘ THEN INCOME END) AS [星期二],SUM(CASE WEEK WHEN ‘星期三‘ THEN INCOME END) AS [星期三],SUM(CASE WEEK WHEN ‘星期四‘ THEN INCOME END) AS [星期四],SUM(CASE WEEK WHEN ‘星期五‘ THEN INCOME END) AS [星期五],SUM(CASE WEEK WHEN ‘星期六‘ THEN INCOME END) AS [星期六],SUM(CASE WEEK WHEN ‘星期日‘ THEN INCOME END) AS [星期日]FROM WEEK_INCOME

但是，在SQL SERVER 2005中提供了更為簡便的方法，這就是"PIVOT"關係運算子。（相反的“列轉行”是UNPIVOT），以下是使用PIVOT實現“行轉列”的SQL語句

SELECT [星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日]FROM WEEK_INCOMEPIVOT(    SUM(INCOME) for [week] in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日]))TBL

 

 

請參考MSDN中關於PIVOT的用法：

http://technet.microsoft.com/zh-cn/library/ms177410(v=sql.105).aspx

 

但是MSDN上的描述太過於規範嚴肅，我看了半天還沒弄清楚怎樣使用PIVOT，搞不清楚PIVOT裡面的文法的含義。於是又google了很多資料，以及通過上面提到的WEEK_INCOME表例子作了實驗，最終搞清楚了其用法。在網上有篇博文解釋的很好：T-SQL PIVOT語法剖析與實戰，基本上我要寫的就是參照該博文，再加上自己一點個人理解。

要理解PIVOT文法，就是要清楚微軟為什麼這樣設計PIVOT，但我相信是現實需求催生設計思路，所以歸根到底我們還是要弄清楚什麼是“行轉列”：

正常情況下的查詢結果是這樣：

星期一           1000
星期二           2000
星期三           3000
星期四           4000
星期五           5000
星期六           6000
星期日           7000

行轉列後是這樣：

星期一   星期二   星期三   星期四   星期五   星期六   星期日
1000    2000    3000    4000    5000    6000    7000

也就是說，行轉列後，原來的某個列的值變做了列名，在這裡就是原來WEEK列的值“星期一”,"星期二"..."星期日"邊做了列名，而我們需要做的另一個工作就是計算這些列的值（這裡的“計算”其實就是PIVOT裡面的彙總函式(sum,avg等)）

現在結合注釋來分析一下PIVOT文法(在這之前最好看看我上面提到博文：T-SQL PIVOT語法剖析與實戰，裡面說到的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--別名一定要寫

 

以上是我對PIVOT的理解，我盡所能表達出來。不過話說回來，個人的理解的方式也不同，就如我開始看了很多篇博文，都沒有搞清楚PIVOT用法。結果還是硬的通過例子和別人的博文再加上思考才弄懂了，所以如果各位看了本篇之後仍不能理解，那很正常，配合例子再加上自己思考，慢慢的定能理解。