一.本文所涉及的內容（Contents）
二.背景（Contexts）
其實行轉列并不是一個什么新鮮的話題了，甚至已經被大家說到爛了，網上的很多例子多多少少都有些問題，所以我希望能讓大家快速的看到執行的效果，所以在動態列的基礎上再把表、分組字段、行轉列字段、值這四個行轉列固定需要的值變成真正意義的參數化，大家只需要根據自己的環境，設置參數值，馬上就能看到效果了（可以直接跳轉至：“ 參數化動態PIVOT行轉列 ”查看具體的腳本代碼）。行轉列的效果圖如圖1所示：
（圖1：行轉列效果圖）
三.實現代碼（SQL Codes）
(一) 首先我們先創建一個測試表，往里面插入測試數據，返回表記錄如圖2所示：
--
創建測試表
IF
EXISTS
(
SELECT
*
FROM
sys.objects
WHERE
object_id
=
OBJECT_ID
(N
'
[dbo].[TestRows2Columns]
'
)
AND
type
in
(N
'
U
'
))
DROP
TABLE
[
dbo
]
.
[
TestRows2Columns
]
GO
CREATE
TABLE
[
dbo
]
.
[
TestRows2Columns
]
(
[
Id
]
[
int
]
IDENTITY
(
1
,
1
)
NOT
NULL
,
[
UserName
]
[
nvarchar
]
(
50
)
NULL
,
[
Subject
]
[
nvarchar
]
(
50
)
NULL
,
[
Source
]
[
numeric
]
(
18
,
0
)
NULL
)
ON
[
PRIMARY
]
GO
--
插入測試數據
INSERT
INTO
[
TestRows2Columns
]
(
[
UserName
]
,
[
Subject
]
,
[
Source
]
)
SELECT
N
'
張三
'
,N
'
語文
'
,
60
UNION
ALL
SELECT
N
'
李四
'
,N
'
數學
'
,
70
UNION
ALL
SELECT
N
'
王五
'
,N
'
英語
'
,
80
UNION
ALL
SELECT
N
'
王五
'
,N
'
數學
'
,
75
UNION
ALL
SELECT
N
'
王五
'
,N
'
語文
'
,
57
UNION
ALL
SELECT
N
'
李四
'
,N
'
語文
'
,
80
UNION
ALL
SELECT
N
'
張三
'
,N
'
英語
'
,
100
GO
SELECT
*
FROM
[
TestRows2Columns
]
（圖2：樣本數據）
--
1：靜態拼接行轉列
SELECT
[
UserName
]
,
SUM
(
CASE
[
Subject
]
WHEN
'
數學
'
THEN
[
Source
]
ELSE
0
END
)
AS
'
[數學]
'
,
SUM
(
CASE
[
Subject
]
WHEN
'
英語
'
THEN
[
Source
]
ELSE
0
END
)
AS
'
[英語]
'
,
SUM
(
CASE
[
Subject
]
WHEN
'
語文
'
THEN
[
Source
]
ELSE
0
END
)
AS
'
[語文]
'
FROM
[
TestRows2Columns
]
GROUP
BY
[
UserName
]
GO
（圖3：樣本數據）
(三) 接著以動態的方式實現行轉列，這是使用拼接SQL的方式實現的，所以它適用于SQL Server 2000以上的數據庫版本，執行腳本返回的結果如圖2所示；
--
2：動態拼接行轉列
DECLARE
@sql
VARCHAR
(
8000
)
SET
@sql
=
'
SELECT [UserName],
'
SELECT
@sql
=
@sql
+
'
SUM(CASE [Subject] WHEN
'''
+
[
Subject
]
+
'''
THEN [Source] ELSE 0 END) AS
'''
+
QUOTENAME
(
[
Subject
]
)
+
'''
,
'
FROM
(
SELECT
DISTINCT
[
Subject
]
FROM
[
TestRows2Columns
]
)
AS
a
SELECT
@sql
=
LEFT
(
@sql
,
LEN
(
@sql
)
-
1
)
+
'
FROM [TestRows2Columns] GROUP BY [UserName]
'
PRINT
(
@sql
)
EXEC
(
@sql
)
GO
(四) 在SQL Server 2005之后有了一個專門的PIVOT 和 UNPIVOT 關系運算符做行列之間的轉換，下面是靜態的方式實現的，實現效果如圖4所示：
--
3：靜態PIVOT行轉列
SELECT
*
FROM
(
SELECT
[
UserName
]
,
[
Subject
]
,
[
Source
]
FROM
[
TestRows2Columns
]
) p PIVOT
(
SUM
(
[
Source
]
)
FOR
[
Subject
]
IN
(
[
數學
]
,
[
英語
]
,
[
語文
]
) )
AS
pvt
ORDER
BY
pvt.
[
UserName
]
;
GO
（圖4）
(五) 把上面靜態的SQL基礎上進行修改，這樣就不用理會記錄里面存儲了什么，需要轉成什么列名的問題了，腳本如下，效果如圖4所示：
--
4：動態PIVOT行轉列
DECLARE
@sql_str
VARCHAR
(
8000
)
DECLARE
@sql_col
VARCHAR
(
8000
)
SELECT
@sql_col
=
ISNULL
(
@sql_col
+
'
,
'
,
''
)
+
QUOTENAME
(
[
Subject
]
)
FROM
[
TestRows2Columns
]
GROUP
BY
[
Subject
]
SET
@sql_str
=
'
SELECT * FROM (
SELECT [UserName],[Subject],[Source] FROM [TestRows2Columns]) p PIVOT
(SUM([Source]) FOR [Subject] IN (
'
+
@sql_col
+
'
) ) AS pvt
ORDER BY pvt.[UserName]
'
PRINT
(
@sql_str
)
EXEC
(
@sql_str
)
(六) 也許很多人到了上面一步就夠了，但是你會發現，當別人拿到你的代碼，需要不斷的修改成他自己環境中表名、分組列、行轉列字段、字段值這幾個參數，邏輯如圖5所示，所以，我繼續對上面的腳本進行修改，你只要設置自己的參數就可以實現行轉列了，效果如圖4所示：
--
5：參數化動態PIVOT行轉列
--
=============================================
--
Author: <聽風吹雨>
--
Create date: <2014.05.26>
--
Description: <參數化動態PIVOT行轉列>
--
Blog: <http://www.cnblogs.com/gaizai/>
--
=============================================
DECLARE
@sql_str
NVARCHAR
(
MAX
)
DECLARE
@sql_col
NVARCHAR
(
MAX
)
DECLARE
@tableName
SYSNAME
--
行轉列表
DECLARE
@groupColumn
SYSNAME
--
分組字段
DECLARE
@row2column
SYSNAME
--
行變列的字段
DECLARE
@row2columnValue
SYSNAME
--
行變列值的字段
SET
@tableName
=
'
TestRows2Columns
'
SET
@groupColumn
=
'
UserName
'
SET
@row2column
=
'
Subject
'
SET
@row2columnValue
=
'
Source
'
--
從行數據中獲取可能存在的列
SET
@sql_str
=
N
'
SELECT @sql_col_out = ISNULL(@sql_col_out +
''
,
''
,
''''
) + QUOTENAME([
'
+
@row2column
+
'
])
FROM [
'
+
@tableName
+
'
] GROUP BY [
'
+
@row2column
+
'
]
'
--
PRINT @sql_str
EXEC
sp_executesql
@sql_str
,N
'
@sql_col_out NVARCHAR(MAX) OUTPUT
'
,
@sql_col_out
=
@sql_col
OUTPUT
--
PRINT @sql_col
SET
@sql_str
=
N
'
SELECT * FROM (
SELECT [
'
+
@groupColumn
+
'
],[
'
+
@row2column
+
'
],[
'
+
@row2columnValue
+
'
] FROM [
'
+
@tableName
+
'
]) p PIVOT
(SUM([
'
+
@row2columnValue
+
'
]) FOR [
'
+
@row2column
+
'
] IN (
'
+
@sql_col
+
'
) ) AS pvt
ORDER BY pvt.[
'
+
@groupColumn
+
'
]
'
--
PRINT (@sql_str)
EXEC
(
@sql_str
)
（圖5）
(七) 在實際的運用中，我經常遇到需要對基礎表的數據進行篩選后再進行行轉列，那么下面的腳本將滿足你這個需求，效果如圖6所示：
--
6：帶條件查詢的參數化動態PIVOT行轉列
--
=============================================
--
Author: <聽風吹雨>
--
Create date: <2014.05.26>
--
Description: <參數化動態PIVOT行轉列，帶條件查詢的參數化動態PIVOT行轉列>
--
Blog: <http://www.cnblogs.com/gaizai/>
--
=============================================
DECLARE
@sql_str
NVARCHAR
(
MAX
)
DECLARE
@sql_col
NVARCHAR
(
MAX
)
DECLARE
@sql_where
NVARCHAR
(
MAX
)
DECLARE
@tableName
SYSNAME
--
行轉列表
DECLARE
@groupColumn
SYSNAME
--
分組字段
DECLARE
@row2column
SYSNAME
--
行變列的字段
DECLARE
@row2columnValue
SYSNAME
--
行變列值的字段
SET
@tableName
=
'
TestRows2Columns
'
SET
@groupColumn
=
'
UserName
'
SET
@row2column
=
'
Subject
'
SET
@row2columnValue
=
'
Source
'
SET
@sql_where
=
'
WHERE UserName =
''
王五
'''
--
從行數據中獲取可能存在的列
SET
@sql_str
=
N
'
SELECT @sql_col_out = ISNULL(@sql_col_out +
''
,
''
,
''''
) + QUOTENAME([
'
+
@row2column
+
'
])
FROM [
'
+
@tableName
+
'
]
'
+
@sql_where
+
'
GROUP BY [
'
+
@row2column
+
'
]
'
--
PRINT @sql_str
EXEC
sp_executesql
@sql_str
,N
'
@sql_col_out NVARCHAR(MAX) OUTPUT
'
,
@sql_col_out
=
@sql_col
OUTPUT
--
PRINT @sql_col
SET
@sql_str
=
N
'
SELECT * FROM (
SELECT [
'
+
@groupColumn
+
'
],[
'
+
@row2column
+
'
],[
'
+
@row2columnValue
+
'
] FROM [
'
+
@tableName
+
'
]
'
+
@sql_where
+
'
) p PIVOT
(SUM([
'
+
@row2columnValue
+
'
]) FOR [
'
+
@row2column
+
'
] IN (
'
+
@sql_col
+
'
) ) AS pvt
ORDER BY pvt.[
'
+
@groupColumn
+
'
]
'
--
PRINT (@sql_str)
EXEC
(
@sql_str
)
（圖6）
四.參考文獻（References）
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
