數(shù)據(jù)庫表的轉(zhuǎn)置

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

問題

在工作中會(huì)遇到這樣的問題。

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

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

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

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

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

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

表中的每一行表示一個(gè)產(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í)，表中的數(shù)據(jù)如下所示。

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

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

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

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

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

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

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的列中。此時(shí)得到的結(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)系運(yùn)算符得到的pvt結(jié)果集中檢索數(shù)據(jù)，而不是從Sales.Orders中檢索數(shù)據(jù)。

Pivot 的語法如下：

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

需要注意的是：如果聚合函數(shù)與 PIVOT 一起使用，則計(jì)算聚合時(shí)將不考慮出現(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

補(bǔ)充說明：

今天在使用 pivot 的時(shí)候，出現(xiàn)一個(gè)奇怪的事情，轉(zhuǎn)置居然失敗了！

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

create table tbl_marks
(
 markId int identity(1,1) , -- 成績的關(guān)鍵字
 sid int ,   -- 學(xué)生的標(biāo)識(shí)
 cid int ,   -- 課程的標(biāo)識(shí)
 
 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 提供的別名必須要有，雖然沒有實(shí)際的用途，但是 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 將對(duì)表中除了組函數(shù)計(jì)算列和轉(zhuǎn)置的列之外所有的字段進(jìn)行分組，在上邊的情況下，組函數(shù)對(duì) mark 進(jìn)行求值，對(duì)課程列 cid 進(jìn)行轉(zhuǎn)置，而表中實(shí)際有 4 列，剩下了 markit 和 sid 兩列，而 markit 是一個(gè)自增長的標(biāo)識(shí)列，所有的行都不相同，這樣，實(shí)際上的分組并不是在 sid 一個(gè)字段上進(jìn)行的，導(dǎo)致了錯(cuò)誤的轉(zhuǎn)置結(jié)果。

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

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 中子查詢需要提供一個(gè)別名，雖然還是沒有什么用途?！　?/p>

如果使用 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 , -- 僅僅對(duì)課程編號(hào)是 2 的課程成績進(jìn)行求和，其實(shí)課程編號(hào)為 2 的成績僅僅出現(xiàn)了一次
      -- 所以，實(shí)際上返回的就是課程 2 的成績
 avg( case when cid = 3 then mark end ) as xml,
 sum( mark ) as [總分]
from tbl_marks
 
group by [sid] 

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

參考文獻(xiàn)

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

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

本文主要參考一下文章：

張洪舉的文章：在SQL Server 2005中實(shí)現(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 的實(shí)現(xiàn)

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

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