問題 在工作中會遇到這樣的問題。 一個學生有多科成績，每科成績記錄在表中為一條記錄，那么查詢出來之后，要把一個學生所有科目的成績打印成一行，即把表中的多條記錄合成一條記錄。 在銷售管理中，每個月的銷售數字在表中表示為一條記錄，在統計的時候，需

問題

在工作中會遇到這樣的問題。

一個學生有多科成績，每科成績記錄在表中為一條記錄，那么查詢出來之后，要把一個學生所有科目的成績打印成一行，即把表中的多條記錄合成一條記錄。

在銷售管理中，每個月的銷售數字在表中表示為一條記錄，在統計的時候，需要按照產品，在一行中輸出所有月份的銷售統計。

當然對于一些比較死板的人來說可以說那我就做多個字段來記錄就是了，但是這樣的擴展性就很差了。如果科目變了呢，你不得改表結構嗎？

準備數據

下面我們以銷售為例，首先創建如下的表。

create table Orders
(
 ProductID int,
 OrderMonth int,
 SubTotal money
)

表中的每一行表示一個產品每月的銷售情況。

然后，插入若干數據。

insert into Orders ( ProductID, OrderMonth, SubTotal )
select 1, 5, 100.00 union all
select 1, 6, 100.00 union all
select 2, 5, 200.00 union all
select 2, 6, 200.00 union all
select 2, 7, 300.00 union all
select 3, 5, 400.00 union all
select 3, 5, 400.00 

此時，表中的數據如下所示。

現在，我們需要統計每種產品在每個月的銷售情況。

SQL Server2005 中的方式

使用在 SQL Server2005 中提供的 Pivot 進行轉置。

SELECT ProductID, [5] AS 五月, [6] AS 六月, [7] AS 七月
FROM
Orders PIVOT
(
SUM (Orders.SubTotal)
FOR Orders.OrderMonth IN
( [5], [6], [7] )
) AS pvt
 
ORDER BY ProductID;

Orders PIVOT 表示對表 Orders 進行轉置操作，這個表稱為輸入表。

Orders.OrderMonth 稱為透視列（pivot_column），FOR Orders.OrderMonth IN ( [5], [6], [7] ) 表示針對表中 OrderMonth 為 5，6，7 的月進行分組之后轉置。

SUM (Orders.SubTotal) 表示針對每組的 SubTotal 進行分組求和。SubTotal 列稱為值列。

SELECT ProductID, [5] AS 五月, [6] AS 六月, [7] AS 七月 語句中的 ProductId 與 OrderMonth 組合在一起完成分組，后面的 [5] AS 五月, [6] AS 六月, [7] AS 七月 分別對應 FOR Orders.OrderMonth IN ( [5], [6], [7] ) 中的 [5], [6], [7] 。

Pivot 實際上按照以下的步驟完成操作：

1. 分組求和，先按照 ProductID,OrderMonth 進行分組求和

SELECT ProductID, OrderMonth, SUM (Orders.SubTotal) AS SumSubTotal
FROM Orders
GROUP BY ProductID,OrderMonth;

得到的結果如下：

2. PIVOT根據FOR OrderMonth IN指定的值5、6、7，首先在結果集中建立名為5、6、7的列，然后從上面的結果中取出 OrderMonth 列中取出相符合的值，分別放置到5、6、7的列中。此時得到的結果集的別名為pvt（見語句中AS pvt的指定）。結果集的內容如下所示。

3. 最后根據SELECT ProductID, [5] AS 五月, [6] AS 六月, [7] AS 七月 FROM的指定，從別名pvt結果集中檢索數據，并分別將名為5、6、7的列在最終結果集中重新命名為五月、六月、七月。這里需要注意的是FROM的含 義，其表示從經PIVOT關系運算符得到的pvt結果集中檢索數據，而不是從Sales.Orders中檢索數據。

Pivot 的語法如下：

SELECT <非透視的列>,
 [第一個透視的列] AS <列名稱>,
 [第二個透視的列] AS <列名稱>,
 ...
 [最后一個透視的列] AS <列名稱>,
FROM
 (<生成數據的 SELECT 查詢>)
 AS <源查詢的別名>
PIVOT
 ( <聚合函數>(<要聚合的列>)
FOR
 
[<包含要成為列標題的值的列>]
 IN ( [第一個透視的列], [第二個透視的列], ... [最后一個透視的列])
)
 AS <透視表的別名>
<可選的 ORDER BY 子句>;

需要注意的是：如果聚合函數與 PIVOT 一起使用，則計算聚合時將不考慮出現在值列中的任何空值。

SQL Server 2000 中的方式

在 SQL Server 2005 之前，通常需要通過 case 子句來處理。

select ProductID,
 sum( case when OrderMonth = 5 then SubTotal end ) as 五月,
 sum( case when OrderMonth = 6 then SubTotal end ) as 六月,
 sum( case when OrderMonth = 7 then SubTotal end ) as 七月
from Orders
group by ProductID

補充說明：

今天在使用 pivot 的時候，出現一個奇怪的事情，轉置居然失敗了！

表的結構如下：

create table tbl_marks
(
 markId int identity(1,1) , -- 成績的關鍵字
 sid int , -- 學生的標識
 cid int , -- 課程的標識
 
 mark int, -- 成績
)

表中的數據為：

insert into tbl_marks ( sid, cid, mark ) values ( 1, 1, 100 );
insert into tbl_marks ( sid, cid, mark ) values ( 1, 2, 90 );
insert into tbl_marks ( sid, cid, mark ) values ( 1, 3, 95 );
insert into tbl_marks ( sid, cid, mark ) values ( 2, 1, 60 );
insert into tbl_marks ( sid, cid, mark ) values ( 2, 2, 61 );
insert into tbl_marks ( sid, cid, mark ) values ( 2, 3, 99 );

執行的轉置語句如下：

select sid, [1] as [database], [2] as [CSharp], [3] as [Xml]
from tbl_marks pivot
(
 sum( mark )
 for cid in ( [1], [2], [3] )
) as pvt

注意，最后的 as pvt 提供的別名必須要有，雖然沒有實際的用途，但是 SQLServer 2005 要求必須提供。

結果如下：

1 100 NULL NULL
1 NULL 90 NULL
1 NULL NULL 95
2 60 NULL NULL
2 NULL 61 NULL
2 NULL NULL 99

應該只有兩行的結果，居然是六行，顯然沒有進行分組！

經過分析，發現在 pivot 中，分組的依據是隱含的，pivot 將對表中除了組函數計算列和轉置的列之外所有的字段進行分組，在上邊的情況下，組函數對 mark 進行求值，對課程列 cid 進行轉置，而表中實際有 4 列，剩下了 markit 和 sid 兩列，而 markit 是一個自增長的標識列，所有的行都不相同，這樣，實際上的分組并不是在 sid 一個字段上進行的，導致了錯誤的轉置結果。

處理的方法是將表中的列在 3 列，排除掉 markit 這個標識列，通過子查詢可以輕松處理這個問題。

select sid, [1] as [database], [2] as [CSharp], [3] as [Xml]
from ( select sid, cid, mark from tbl_marks) t pivot
(
 sum( mark )
 for cid in ( [1], [2], [3] )
) as pvt

注意，在 SQLServer 中子查詢需要提供一個別名，雖然還是沒有什么用途。　　

如果使用 case 的話，上邊的轉置還可以這樣寫。

select [sid], 
 max( case when cid = 1 then mark end ) as [database],
 min( case when cid = 2 then mark end ) as cSharp , -- 僅僅對課程編號是 2 的課程成績進行求和，其實課程編號為 2 的成績僅僅出現了一次
 -- 所以，實際上返回的就是課程 2 的成績
 avg( case when cid = 3 then mark end ) as xml,
 sum( mark ) as [總分]
from tbl_marks
 
group by [sid] 

在這個例子中，組函數不僅可以使用 sum, 其實使用 max, min, avg 都可以，你知道為什么嗎？　　

參考文獻

詳細的 case 使用說明可以參考 錢途無梁 的 sql 中 case when 語法

http://www.cnblogs.com/qiantuwuliang/archive/2009/06/03/1495770.html

本文主要參考一下文章：

張洪舉的文章：在SQL Server 2005中實現表的行列轉換

http://blog.csdn.net/zhanghongju/archive/2006/06/02/769445.aspx

MSDN: 使用 PIVOT 和 UNPIVOT

http://technet.microsoft.com/zh-cn/library/ms177410.aspx

Sman Sky ：表中數據轉置（Pivot）在Sql Server 2000 和Sql Server 2005 的實現

http://www.cnblogs.com/huangbaixun/archive/2008/07/26/1252002.html

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com