原文:
SQL Server 动态行转列（参数化表名、分组列、行转列字段、字段值）
一.本文所涉及的内容（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）
