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

問題

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

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

在銷售管理中，每個月的銷售數(shù)字在表中表示為一條記錄，在統(tǒng)計的時候，需要按照產(chǎn)品，在一行中輸出所有月份的銷售統(tǒng)計。

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

準(zhǔn)備數(shù)據(jù)

下面我們以銷售為例，首先創(chuàng)建如下的表。

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

表中的每一行表示一個產(chǎn)品每月的銷售情況。

然后，插入若干數(shù)據(jù)。

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 

此時，表中的數(shù)據(jù)如下所示。

現(xiàn)在，我們需要統(tǒng)計每種產(chǎn)品在每個月的銷售情況。

SQL Server2005 中的方式

使用在 SQL Server2005 中提供的 Pivot 進(jìn)行轉(zhuǎn)置。

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 進(jìn)行轉(zhuǎn)置操作，這個表稱為輸入表。

Orders.OrderMonth 稱為透視列（pivot_column），F(xiàn)OR Orders.OrderMonth IN ( [5], [6], [7] ) 表示針對表中 OrderMonth 為 5，6，7 的月進(jìn)行分組之后轉(zhuǎn)置。

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

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

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

1. 分組求和，先按照 ProductID,OrderMonth 進(jìn)行分組求和

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

得到的結(jié)果如下：

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

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

Pivot 的語法如下：

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

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

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 的時候，出現(xiàn)一個奇怪的事情，轉(zhuǎn)置居然失敗了！

表的結(jié)構(gòu)如下：

create table tbl_marks
(
 markId int identity(1,1) , -- 成績的關(guān)鍵字
 sid int , -- 學(xué)生的標(biāo)識
 cid int , -- 課程的標(biāo)識
 
 mark int, -- 成績
)

表中的數(shù)據(jù)為：

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 );

執(zhí)行的轉(zhuǎn)置語句如下：

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 要求必須提供。

結(jié)果如下：

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

應(yīng)該只有兩行的結(jié)果，居然是六行，顯然沒有進(jìn)行分組！

經(jīng)過分析，發(fā)現(xiàn)在 pivot 中，分組的依據(jù)是隱含的，pivot 將對表中除了組函數(shù)計算列和轉(zhuǎn)置的列之外所有的字段進(jìn)行分組，在上邊的情況下，組函數(shù)對 mark 進(jìn)行求值，對課程列 cid 進(jìn)行轉(zhuǎn)置，而表中實際有 4 列，剩下了 markit 和 sid 兩列，而 markit 是一個自增長的標(biāo)識列，所有的行都不相同，這樣，實際上的分組并不是在 sid 一個字段上進(jìn)行的，導(dǎo)致了錯誤的轉(zhuǎn)置結(jié)果。

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

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 的話，上邊的轉(zhuǎn)置還可以這樣寫。

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

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

參考文獻(xiàn)

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

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

本文主要參考一下文章：

張洪舉的文章：在SQL Server 2005中實現(xiàn)表的行列轉(zhuǎn)換

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 ：表中數(shù)據(jù)轉(zhuǎn)置（Pivot）在Sql Server 2000 和Sql Server 2005 的實現(xiàn)

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

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