--创建测试表 2 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N‘[dbo].[TestRows2Columns]‘) AND type in (N‘U‘)) 3 DROP TABLE [dbo].[TestRows2Columns] 4 GO 5 CREATE TABLE [dbo].[TestRows2Columns]( 6 [Id] [int] IDENTITY(1,1) NOT NULL, 7 [UserName] [nvarchar](50) NULL, 8 [Subject] [nvarchar](50) NULL, 9 [Source] [numeric](18, 0) NULL 10 ) ON [PRIMARY] 11 GO 12 13 --插入测试数据 14 INSERT INTO [TestRows2Columns] ([UserName],[Subject],[Source]) 15 SELECT N‘张三‘,N‘语文‘,60 UNION ALL 16 SELECT N‘李四‘,N‘数学‘,70 UNION ALL 17 SELECT N‘王五‘,N‘英语‘,80 UNION ALL 18 SELECT N‘王五‘,N‘数学‘,75 UNION ALL 19 SELECT N‘王五‘,N‘语文‘,57 UNION ALL 20 SELECT N‘李四‘,N‘语文‘,80 UNION ALL 21 SELECT N‘张三‘,N‘英语‘,100 22 GO 23 24 SELECT * FROM [TestRows2Columns]

（图2：样本数据）

(二) 先以静态的方式实现行转列，效果如图3所示：

1 --1：静态拼接行转列
2 SELECT [UserName],
3 SUM(CASE [Subject] WHEN ‘数学‘ THEN [Source] ELSE 0 END) AS ‘[数学]‘,
4 SUM(CASE [Subject] WHEN ‘英语‘ THEN [Source] ELSE 0 END) AS ‘[英语]‘,
5 SUM(CASE [Subject] WHEN ‘语文‘ THEN [Source] ELSE 0 END) AS ‘[语文]‘     
6 FROM [TestRows2Columns]
7 GROUP BY [UserName]
8 GO

 

（图3：样本数据）

(三) 接着以动态的方式实现行转列，这是使用拼接SQL的方式实现的，所以它适用于SQL Server 2000以上的数据库版本，执行脚本返回的结果如图2所示；

1 --2：动态拼接行转列
2 DECLARE @sql VARCHAR(8000)
3 SET @sql = ‘SELECT [UserName],‘   
4 SELECT @sql = @sql + ‘SUM(CASE [Subject] WHEN ‘‘‘+[Subject]+‘‘‘ THEN [Source] ELSE 0 END) AS ‘‘‘+QUOTENAME([Subject])+‘‘‘,‘   
5 FROM (SELECT DISTINCT [Subject] FROM [TestRows2Columns]) AS a     
6 SELECT @sql = LEFT(@sql,LEN(@sql)-1) + ‘ FROM [TestRows2Columns] GROUP BY [UserName]‘   
7 PRINT(@sql)
8 EXEC(@sql)
9 GO

 

(四) 在SQL Server 2005之后有了一个专门的PIVOT 和 UNPIVOT 关系运算符做行列之间的转换，下面是静态的方式实现的，实现效果如图4所示：

 1 --3：静态PIVOT行转列
 2 SELECT  *
 3 FROM    ( SELECT    [UserName] ,
 4                     [Subject] ,
 5                     [Source]
 6           FROM      [TestRows2Columns]
 7         ) p PIVOT
 8 ( SUM([Source]) FOR [Subject] IN ( [数学],[英语],[语文] ) ) AS pvt
 9 ORDER BY pvt.[UserName];
10 GO

（图4）

(五) 把上面静态的SQL基础上进行修改，这样就不用理会记录里面存储了什么，需要转成什么列名的问题了，脚本如下，效果如图4所示：

 1 --4：动态PIVOT行转列
 2 DECLARE @sql_str VARCHAR(8000)
 3 DECLARE @sql_col VARCHAR(8000)
 4 SELECT @sql_col = ISNULL(@sql_col + ‘,‘,‘‘) + QUOTENAME([Subject]) FROM [TestRows2Columns] GROUP BY [Subject]
 5 SET @sql_str = ‘
 6 SELECT * FROM (
 7     SELECT [UserName],[Subject],[Source] FROM [TestRows2Columns]) p PIVOT 
 8     (SUM([Source]) FOR [Subject] IN ( ‘+ @sql_col +‘) ) AS pvt 
 9 ORDER BY pvt.[UserName]‘
10 PRINT (@sql_str)
11 EXEC (@sql_str)

 

(六) 也许很多人到了上面一步就够了，但是你会发现，当别人拿到你的代码，需要不断的修改成他自己环境中表名、分组列、行转列字段、字段值这几个参数，逻辑如图5所示，

 1 --5：参数化动态PIVOT行转列
 2 -- =============================================
 3 -- Author:        <听风吹雨>
 4 -- Create date: <2014.05.26>
 5 -- Description:    <参数化动态PIVOT行转列>
 6 -- Blog:        <http://www.cnblogs.com/gaizai/>
 7 -- =============================================
 8 DECLARE @sql_str NVARCHAR(MAX)
 9 DECLARE @sql_col NVARCHAR(MAX)
10 DECLARE @tableName SYSNAME --行转列表
11 DECLARE @groupColumn SYSNAME --分组字段
12 DECLARE @row2column SYSNAME --行变列的字段
13 DECLARE @row2columnValue SYSNAME --行变列值的字段
14 SET @tableName = ‘TestRows2Columns‘
15 SET @groupColumn = ‘UserName‘
16 SET @row2column = ‘Subject‘
17 SET @row2columnValue = ‘Source‘
18 
19 --从行数据中获取可能存在的列
20 SET @sql_str = N‘
21 SELECT @sql_col_out = ISNULL(@sql_col_out + ‘‘,‘‘,‘‘‘‘) + QUOTENAME([‘+@row2column+‘]) 
22     FROM [‘+@tableName+‘] GROUP BY [‘+@row2column+‘]‘
23 --PRINT @sql_str
24 EXEC sp_executesql @sql_str,N‘@sql_col_out NVARCHAR(MAX) OUTPUT‘,@sql_col_out=@sql_col OUTPUT
25 --PRINT @sql_col
26 
27 SET @sql_str = N‘
28 SELECT * FROM (
29     SELECT [‘+@groupColumn+‘],[‘+@row2column+‘],[‘+@row2columnValue+‘] FROM [‘+@tableName+‘]) p PIVOT 
30     (SUM([‘+@row2columnValue+‘]) FOR [‘+@row2column+‘] IN ( ‘+ @sql_col +‘) ) AS pvt 
31 ORDER BY pvt.[‘+@groupColumn+‘]‘
32 --PRINT (@sql_str)
33 EXEC (@sql_str)

（图5）

所以，我继续对上面的脚本进行修改，你只要设置自己的参数就可以实现行转列了，效果如图4所示：

(七) 在实际的运用中，我经常遇到需要对基础表的数据进行筛选后再进行行转列，那么下面的脚本将满足你这个需求，效果如图6所示：

 

 1 --6：带条件查询的参数化动态PIVOT行转列
 2 -- =============================================
 3 -- Author:        <听风吹雨>
 4 -- Create date: <2014.05.26>
 5 -- Description:    <参数化动态PIVOT行转列，带条件查询的参数化动态PIVOT行转列>
 6 -- Blog:        <http://www.cnblogs.com/gaizai/>
 7 -- =============================================
 8 DECLARE @sql_str NVARCHAR(MAX)
 9 DECLARE @sql_col NVARCHAR(MAX)
10 DECLARE @sql_where NVARCHAR(MAX)
11 DECLARE @tableName SYSNAME --行转列表
12 DECLARE @groupColumn SYSNAME --分组字段
13 DECLARE @row2column SYSNAME --行变列的字段
14 DECLARE @row2columnValue SYSNAME --行变列值的字段
15 SET @tableName = ‘TestRows2Columns‘
16 SET @groupColumn = ‘UserName‘
17 SET @row2column = ‘Subject‘
18 SET @row2columnValue = ‘Source‘
19 SET @sql_where = ‘WHERE UserName = ‘‘王五‘‘‘
20 
21 --从行数据中获取可能存在的列
22 SET @sql_str = N‘
23 SELECT @sql_col_out = ISNULL(@sql_col_out + ‘‘,‘‘,‘‘‘‘) + QUOTENAME([‘+@row2column+‘]) 
24     FROM [‘+@tableName+‘] ‘+@sql_where+‘ GROUP BY [‘+@row2column+‘]‘
25 --PRINT @sql_str
26 EXEC sp_executesql @sql_str,N‘@sql_col_out NVARCHAR(MAX) OUTPUT‘,@sql_col_out=@sql_col OUTPUT
27 --PRINT @sql_col
28 
29 SET @sql_str = N‘
30 SELECT * FROM (
31     SELECT [‘+@groupColumn+‘],[‘+@row2column+‘],[‘+@row2columnValue+‘] FROM [‘+@tableName+‘]‘+@sql_where+‘) p PIVOT 
32     (SUM([‘+@row2columnValue+‘]) FOR [‘+@row2column+‘] IN ( ‘+ @sql_col +‘) ) AS pvt 
33 ORDER BY pvt.[‘+@groupColumn+‘]‘
34 --PRINT (@sql_str)
35 EXEC (@sql_str)

（图6）

四.参考文献（References）

使用 PIVOT 和 UNPIVOT

注：本文转载自 听风吹雨 网址：http://www.cnblogs.com/gaizai/p/3753296.html#_labelFive

SQL Server 动态行转列（参数化表名、分组列、行转列字段、字段值）

标签：分组   group   max   条件过滤   isnull   技术分享   扩展   关系运算   int