行轉(zhuǎn)列，列轉(zhuǎn)行是我們在開發(fā)過程中經(jīng)常碰到的問題。行轉(zhuǎn)列一般通過CASE WHEN 語句來實現(xiàn)，也可以通過 SQL SERVER 2005 新增的運算符PIVOT來實現(xiàn)。 用傳統(tǒng)的方法，比較好理解。層次清晰，而且比較習(xí)慣。 但是PIVOT 、UNPIVOT提供的語法比一系列復(fù)雜的 SELECT...CASE 語句中所指定的語法更簡單、更具可讀性。下面我們通過幾個簡單的例子來介紹一下列轉(zhuǎn)行、行轉(zhuǎn)列問題。

我們首先先通過一個老生常談的例子，學(xué)生成績表(下面簡化了些)來形象了解下行轉(zhuǎn)列?

代碼 CREATE??TABLE?[StudentScores]
(
????[UserName]?? ? ? ??NVARCHAR(20),????????--學(xué)生姓名
????[Subject]?? ? ? ? ?NVARCHAR(30),????????--科目
????[Score]????????????FLOAT,???????????????--成績
)

INSERT?INTO?[StudentScores]?SELECT?'Nick',?'語文',?80

INSERT?INTO?[StudentScores]?SELECT?'Nick',?'數(shù)學(xué)',?90

INSERT?INTO?[StudentScores]?SELECT?'Nick',?'英語',?70

INSERT?INTO?[StudentScores]?SELECT?'Nick',?'生物',?85

INSERT?INTO?[StudentScores]?SELECT?'Kent',?'語文',?80

INSERT?INTO?[StudentScores]?SELECT?'Kent',?'數(shù)學(xué)',?90

INSERT?INTO?[StudentScores]?SELECT?'Kent',?'英語',?70

INSERT?INTO?[StudentScores]?SELECT?'Kent',?'生物',?85

?

?如果我想知道每位學(xué)生的每科成績，而且每個學(xué)生的全部成績排成一行，這樣方便我查看、統(tǒng)計，導(dǎo)出數(shù)據(jù)

代碼 SELECT?
??????UserName,?
??????MAX(CASE?Subject?WHEN?'語文'?THEN?Score?ELSE?0?END)?AS?'語文',
??????MAX(CASE?Subject?WHEN?'數(shù)學(xué)'?THEN?Score?ELSE?0?END)?AS?'數(shù)學(xué)',
??????MAX(CASE?Subject?WHEN?'英語'?THEN?Score?ELSE?0?END)?AS?'英語',
??????MAX(CASE?Subject?WHEN?'生物'?THEN?Score?ELSE?0?END)?AS?'生物'
FROM?dbo.[StudentScores]
GROUP?BY?UserName

查詢結(jié)果如圖所示，這樣我們就能很清楚的了解每位學(xué)生所有的成績了

?

?

接下來我們來看看第二個小列子。有一個游戲玩家充值表（僅僅為了說明，舉的一個小例子），

?代碼

CREATE?TABLE?[Inpours]
(
????[ID]???????????? INT?IDENTITY(1,1),?
????[UserName]?? ? ? ? ?NVARCHAR(20),??--游戲玩家
????[CreateTime]???? DATETIME,??????--充值時間????
????[PayType]???????? NVARCHAR(20),??--充值類型????
????[Money]?? ? ? ? ? ??DECIMAL,???????--充值金額
????[IsSuccess]?? ? ? ??BIT,???????????--是否成功?1表示成功，?0表示失敗
????CONSTRAINT?[PK_Inpours_ID]?PRIMARY?KEY(ID)
)

INSERT?INTO?Inpours?SELECT?'張三',?'2010-05-01',?'支付寶',?50,?1

INSERT?INTO?Inpours?SELECT?'張三',?'2010-06-14',?'支付寶',?50,?1

INSERT?INTO?Inpours?SELECT?'張三',?'2010-06-14',?'手機短信',?100,?1

INSERT?INTO?Inpours?SELECT?'李四',?'2010-06-14',?'手機短信',?100,?1

INSERT?INTO?Inpours?SELECT?'李四',?'2010-07-14',?'支付寶',?100,?1

INSERT?INTO?Inpours?SELECT?'王五',?'2010-07-14',?'工商銀行卡',?100,?1

INSERT?INTO?Inpours?SELECT?'趙六',?'2010-07-14',?'建設(shè)銀行卡',?100,?1

?

下面來了一個統(tǒng)計數(shù)據(jù)的需求，要求按日期、支付方式來統(tǒng)計充值金額信息。這也是一個典型的行轉(zhuǎn)列的例子。我們可以通過下面的腳本來達到目的 代碼 SELECT?CONVERT(VARCHAR(10),?CreateTime,?120)?AS?CreateTime,
???????CASE?PayType?WHEN?'支付寶'?? ? THEN?SUM(Money)?ELSE?0?END?AS?'支付寶',
???????CASE?PayType?WHEN?'手機短信'?? ?THEN?SUM(Money)?ELSE?0?END?AS?'手機短信',
???????CASE?PayType?WHEN?'工商銀行卡'??THEN?SUM(Money)?ELSE?0?END?AS?'工商銀行卡',
???????CASE?PayType?WHEN?'建設(shè)銀行卡'??THEN?SUM(Money)?ELSE?0?END?AS?'建設(shè)銀行卡'
FROM?Inpours
GROUP?BY?CreateTime,?PayType

?

?如圖所示，我們這樣只是得到了這樣的輸出結(jié)果，還需進一步處理，才能得到想要的結(jié)果

代碼
SELECT?
???????CreateTime,?
???????ISNULL(SUM([支付寶]),?0)?AS?[支付寶],?
???????ISNULL(SUM([手機短信]),?0)?AS?[手機短信],
???????ISNULL(SUM([工商銀行卡]),?0)?AS?[工商銀行卡],?
???????ISNULL(SUM([建設(shè)銀行卡]),?0)?AS?[建設(shè)銀行卡]
FROM
(
????SELECT?CONVERT(VARCHAR(10),?CreateTime,?120)?AS?CreateTime,
???????????CASE?PayType?WHEN?'支付寶'?????THEN?SUM(Money)?ELSE?0?END?AS?'支付寶',
???????????CASE?PayType?WHEN?'手機短信'???THEN?SUM(Money)?ELSE?0?END?AS?'手機短信',
???????????CASE?PayType?WHEN?'工商銀行卡'?THEN?SUM(Money)?ELSE?0?END?AS?'工商銀行卡',
???????????CASE?PayType?WHEN?'建設(shè)銀行卡'?THEN?SUM(Money)?ELSE?0?END?AS?'建設(shè)銀行卡'
????FROM?Inpours
????GROUP?BY?CreateTime,?PayType
)?T
GROUP?BY?CreateTime

?

其實行轉(zhuǎn)列，關(guān)鍵是要理清邏輯，而且對分組（Group by）概念比較清晰。上面兩個列子基本上就是行轉(zhuǎn)列的類型了。但是有個問題來了，上面是我為了說明弄的一個簡單列子。實際中，可能支付方式特別多，而且邏輯也復(fù)雜很多，可能涉及匯率、手續(xù)費等等(曾經(jīng)做個這樣一個)，如果支付方式特別多，我們的CASE WHEN 會弄出一大堆，確實比較惱火，而且新增一種支付方式，我們還得修改腳本如果把上面的腳本用動態(tài)SQL改寫一下，我們就能輕松解決這個問題?

代碼 DECLARE?@cmdText????VARCHAR(8000);
DECLARE?@tmpSql????????VARCHAR(8000);

SET?@cmdText?=?'SELECT?CONVERT(VARCHAR(10),?CreateTime,?120)?AS?CreateTime,'?+?CHAR(10);
SELECT?@cmdText?=?@cmdText?+?'?CASE?PayType?WHEN?'''?+?PayType?+?'''?THEN?SUM(Money)?ELSE?0?END?AS?'''?+?PayType?
????????????????+?''','?+?CHAR(10)??FROM?(SELECT?DISTINCT?PayType?FROM?Inpours?)?T

SET?@cmdText?=?LEFT(@cmdText,?LEN(@cmdText)?-2)?--注意這里，如果沒有加CHAR(10)?則用LEFT(@cmdText,?LEN(@cmdText)?-1)

SET?@cmdText?=?@cmdText?+?'?FROM?Inpours?????GROUP?BY?CreateTime,?PayType?';

SET?@tmpSql?='SELECT?CreateTime,'?+?CHAR(10);
SELECT?@tmpSql?=?@tmpSql?+?'?ISNULL(SUM('?+?PayType??+?'),?0)?AS?'''?+?PayType??+?''','??+?CHAR(10)
????????????????????FROM??(SELECT?DISTINCT?PayType?FROM?Inpours?)?T

SET?@tmpSql?=?LEFT(@tmpSql,?LEN(@tmpSql)?-2)?+?'?FROM?('?+?CHAR(10);

SET?@cmdText?=?@tmpSql?+?@cmdText?+?')?T?GROUP?BY?CreateTime?';
PRINT?@cmdText
EXECUTE?(@cmdText);

?

下面是通過PIVOT來進行行轉(zhuǎn)列的用法，大家可以對比一下，確實要簡單、更具可讀性（呵呵，習(xí)慣的前提下）

代碼 SELECT?
????????CreateTime,?[支付寶]?,?[手機短信],
????????[工商銀行卡]?,?[建設(shè)銀行卡]
FROM
(
????SELECT?CONVERT(VARCHAR(10),?CreateTime,?120)?AS?CreateTime,PayType,?Money
????FROM?Inpours
)?P
PIVOT?(
????????????SUM(Money)
????????????FOR?PayType?IN
????????????([支付寶],?[手機短信],?[工商銀行卡],?[建設(shè)銀行卡])
??????)?AS?T
ORDER?BY?CreateTime

?

有時可能會出現(xiàn)這樣的錯誤：

消息 325，級別 15，狀態(tài) 1，第 9 行

'PIVOT' 附近有語法錯誤。您可能需要將當前數(shù)據(jù)庫的兼容級別設(shè)置為更高的值，以啟用此功能。有關(guān)存儲過程 sp_dbcmptlevel 的信息，請參見幫助。

這個是因為：對升級到 SQL Server 2005 或更高版本的數(shù)據(jù)庫使用 PIVOT 和 UNPIVOT 時，必須將數(shù)據(jù)庫的兼容級別設(shè)置為 90 或更高。有關(guān)如何設(shè)置數(shù)據(jù)庫兼容級別的信息，請參閱 。 例如，只需在執(zhí)行上面腳本前加上 EXEC sp_dbcmptlevel Test, 90; 就OK了， Test 是所在數(shù)據(jù)庫的名稱。

?

下面我們來看看列轉(zhuǎn)行，主要是通過UNION ALL ,MAX來實現(xiàn)。假如有下面這么一個表

代碼 CREATE?TABLE?ProgrectDetail
(
????ProgrectName ? ? ? ??NVARCHAR(20),?--工程名稱
????OverseaSupply????????INT,??????????--海外供應(yīng)商供給數(shù)量
????NativeSupply ? ? ? ??INT,??????????--國內(nèi)供應(yīng)商供給數(shù)量
????SouthSupply??????????INT,??????????--南方供應(yīng)商供給數(shù)量
????NorthSupply ? ? ? ? ?INT???????????--北方供應(yīng)商供給數(shù)量
)

INSERT?INTO?ProgrectDetail
SELECT?'A',?100,?200,?50,?50
UNION?ALL
SELECT?'B',?200,?300,?150,?150
UNION?ALL
SELECT?'C',?159,?400,?20,?320
UNION?ALL
SELECT?'D',?250,?30,?15,?15

?

?我們可以通過下面的腳本來實現(xiàn)，查詢結(jié)果如下圖所示

代碼 SELECT?ProgrectName,?'OverseaSupply'?AS?Supplier,
????????MAX(OverseaSupply)?AS?'SupplyNum'
FROM?ProgrectDetail
GROUP?BY?ProgrectName
UNION?ALL
SELECT?ProgrectName,?'NativeSupply'?AS?Supplier,
????????MAX(NativeSupply)?AS?'SupplyNum'
FROM?ProgrectDetail
GROUP?BY?ProgrectName
UNION?ALL
SELECT?ProgrectName,?'SouthSupply'?AS?Supplier,
????????MAX(SouthSupply)?AS?'SupplyNum'
FROM?ProgrectDetail
GROUP?BY?ProgrectName
UNION?ALL
SELECT?ProgrectName,?'NorthSupply'?AS?Supplier,
????????MAX(NorthSupply)?AS?'SupplyNum'
FROM?ProgrectDetail
GROUP?BY?ProgrectName

?

?

用UNPIVOT 實現(xiàn)如下：

代碼 SELECT?ProgrectName,Supplier,SupplyNum
FROM?
(
????SELECT?ProgrectName,?OverseaSupply,?NativeSupply,
???????????SouthSupply,?NorthSupply
?????FROM?ProgrectDetail
)T
UNPIVOT?
(
????SupplyNum?FOR?Supplier?IN
????(OverseaSupply,?NativeSupply,?SouthSupply,?NorthSupply?)
)?P

總結(jié)

以上是生活随笔為你收集整理的重温SQL——行转列，列转行（转：http://www.cnblogs.com/kerrycode/archive/2010/07/28/1786547.html）...的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。