行轉列，列轉行是我們在開發過程中經常碰到的問題。行轉列一般通過 CASE WHEN 語句來實作，也可以通過 SQL SERVER 2005 新增的運算符 PIVOT來實作。 用傳統的方法，比較好了解。層次清晰，而且比較習慣。 但是PIVOT 、 UNPIVOT提供的文法比一系列複雜的 SELECT...CASE 語句中所指定的文法更簡單、更具可讀性。下面我們通過幾個簡單的例子來介紹一下列轉行、行轉列問題。
我們首先先通過一個老生常談的例子，學生成績表(下面簡化了些)來形象了解下行轉列
代碼 CREATE TABLE [ StudentScores ]
(
[ UserName ] NVARCHAR ( 20 ), -- 學生姓名
[ Subject ] NVARCHAR ( 30 ), -- 科目
[ Score ] FLOAT , -- 成績
)
INSERT INTO [ StudentScores ] SELECT ' Nick ' , ' 國文 ' , 80
INSERT INTO [ StudentScores ] SELECT ' Nick ' , ' 數學 ' , 90
INSERT INTO [ StudentScores ] SELECT ' Nick ' , ' 英語 ' , 70
INSERT INTO [ StudentScores ] SELECT ' Nick ' , ' 生物 ' , 85
INSERT INTO [ StudentScores ] SELECT ' Kent ' , ' 國文 ' , 80
INSERT INTO [ StudentScores ] SELECT ' Kent ' , ' 數學 ' , 90
INSERT INTO [ StudentScores ] SELECT ' Kent ' , ' 英語 ' , 70
INSERT INTO [ StudentScores ] SELECT ' Kent ' , ' 生物 ' , 85
如果我想知道每位學生的每科成績，而且每個學生的全部成績排成一行，這樣友善我檢視、統計，導出資料
代碼 SELECT
UserName,
MAX ( CASE Subject WHEN ' 國文 ' THEN Score ELSE 0 END ) AS ' 國文 ' ,
MAX ( CASE Subject WHEN ' 數學 ' THEN Score ELSE 0 END ) AS ' 數學 ' ,
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
查詢結果如圖所示，這樣我們就能很清楚的了解每位學生所有的成績了
接下來我們來看看第二個小列子。有一個遊戲玩家充值表（僅僅為了說明，舉的一個小例子），
代碼
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 ' , ' 建設銀行卡 ' , 100 , 1
下面來了一個統計資料的需求，要求按日期、支付方式來統計充值金額資訊。這也是一個典型的行轉列的例子。我們可以通過下面的腳本來達到目的 代碼 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 ' 建設銀行卡 ' THEN SUM ( Money ) ELSE 0 END AS ' 建設銀行卡 '
FROM Inpours
GROUP BY CreateTime, PayType
如圖所示，我們這樣隻是得到了這樣的輸出結果，還需進一步處理，才能得到想要的結果
代碼
SELECT
CreateTime,
ISNULL ( SUM ( [ 支付寶 ] ), 0 ) AS [ 支付寶 ] ,
ISNULL ( SUM ( [ 手機短信 ] ), 0 ) AS [ 手機短信 ] ,
ISNULL ( SUM ( [ 工商銀行卡 ] ), 0 ) AS [ 工商銀行卡 ] ,
ISNULL ( SUM ( [ 建設銀行卡 ] ), 0 ) AS [ 建設銀行卡 ]
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 ' 建設銀行卡 ' THEN SUM ( Money ) ELSE 0 END AS ' 建設銀行卡 '
FROM Inpours
GROUP BY CreateTime, PayType
) T
GROUP BY CreateTime
其實行轉列，關鍵是要理清邏輯，而且對分組（Group by）概念比較清晰。上面兩個列子基本上就是行轉列的類型了。但是有個問題來了，上面是我為了說明弄的一個簡單列子。實際中，可能支付方式特别多，而且邏輯也複雜很多，可能涉及匯率、手續費等等(曾經做個這樣一個)，如果支付方式特别多，我們的CASE WHEN 會弄出一大堆，确實比較惱火，而且新增一種支付方式，我們還得修改腳本如果把上面的腳本用動态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來進行行轉列的用法，大家可以對比一下，确實要簡單、更具可讀性（呵呵，習慣的前提下）
代碼 SELECT
CreateTime, [ 支付寶 ] , [ 手機短信 ] ,
[ 工商銀行卡 ] , [ 建設銀行卡 ]
FROM
(
SELECT CONVERT ( VARCHAR ( 10 ), CreateTime, 120 ) AS CreateTime,PayType, Money
FROM Inpours
) P
PIVOT (
SUM ( Money )
FOR PayType IN
( [ 支付寶 ] , [ 手機短信 ] , [ 工商銀行卡 ] , [ 建設銀行卡 ] )
) AS T
ORDER BY CreateTime
有時可能會出現這樣的錯誤：
消息 325，級别 15，狀态 1，第 9 行
'PIVOT' 附近有文法錯誤。您可能需要将目前資料庫的相容級别設定為更高的值，以啟用此功能。有關存儲過程 sp_dbcmptlevel 的資訊，請參見幫助。
這個是因為：對更新到 SQL Server 2005 或更高版本的資料庫使用 PIVOT 和 UNPIVOT 時，必須将資料庫的相容級别設定為 90 或更高。有關如何設定資料庫相容級别的資訊，請參閱 sp_dbcmptlevel (Transact-SQL)。 例如，隻需在執行上面腳本前加上 EXEC sp_dbcmptlevel Test, 90; 就OK了， Test 是所在資料庫的名稱。
下面我們來看看列轉行，主要是通過UNION ALL ,MAX來實作。假如有下面這麼一個表
代碼 CREATE TABLE ProgrectDetail
(
ProgrectName NVARCHAR ( 20 ), -- 工程名稱
OverseaSupply INT , -- 海外供應商供給數量
NativeSupply INT , -- 國内供應商供給數量
SouthSupply INT , -- 南方供應商供給數量
NorthSupply INT -- 北方供應商供給數量
)
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
我們可以通過下面的腳本來實作，查詢結果如下圖所示
代碼 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 實作如下：
代碼 SELECT ProgrectName,Supplier,SupplyNum
FROM
(
SELECT ProgrectName, OverseaSupply, NativeSupply,
SouthSupply, NorthSupply
FROM ProgrectDetail
)T
UNPIVOT
(
SupplyNum FOR Supplier IN
(OverseaSupply, NativeSupply, SouthSupply, NorthSupply )
) P