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

一.本文所涉及的内容(Contents)

本文所涉及的内容(Contents) 背景(Contexts) 实现代码(SQL Codes) 方法一:使用拼接SQL,静态列字段; 方法二:使用拼接SQL,动态列字段; 方法三:使用PIVOT关系运算符,静态列字段; 方法四:使用PIVOT关系运算符,动态列字段; 扩展阅读一:参数化表名、分组列、行转列字段、字段值; 扩展阅读二:在前面的基础上加入条件过滤; 参考文献(References)

二.背景(Contexts)

其实行转列并不是一个什么新鲜的话题了,甚至已经被大家说到烂了,网上的很多例子多多少少都有些问题,所以我希望能让大家快速的看到执行的效果,所以在动态列的基础上再把表、分组字段、行转列字段、值这四个行转列固定需要的值变成真正意义的参数化,大家只需要根据自己的环境,设置参数值,马上就能看到效果了(可以直接跳转至:“参数化动态PIVOT行转列”查看具体的脚本代码)。行转列的效果图如图1所示:

(图1:行转列效果图)

三.实现代码(SQL Codes)

(一) 首先我们先创建一个测试表,往里面插入测试数据,返回表记录如图2所示:

 1 --创建测试表
 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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Linux运维学习之路

MySQL索引

索引管理 索引是什么? 索引就好比一本书的目录,它会让你更快的找到内容; 让获取的数据更有目的性,从而提高数据库检索数据的性能; 索引建立在表的列上(字段)。 ...

3455
来自专栏芋道源码1024

数据库[分库分表]中间件 Sharding-JDBC 源码分析 —— SQL 解析(四)之插入SQL

本文主要基于 Sharding-JDBC 1.5.0 正式版 1. 概述 2. InsertStatement 3. #parse() 3.1 #parseI...

4097
来自专栏数据库

mysql数据操作语句

?imageMogr2/blur/1x0/quality/75|watermark/1/image/aHR0cDovL29zNzhmNGhueS5ia3QuY2...

1895
来自专栏大数据

mysql联合索引详解

上一篇文章:mysql数据库索引优化 比较简单的是单列索引(b+tree)。遇到多条件查询时,不可避免会使用到多列索引。联合索引又叫复合索引。 b+tree结构...

8489
来自专栏小灰灰

mysql之基本语法

本篇将主要集中在mysql的使用上,包括如何创建标,如何进行insert,update,select,delete,以及一些常见的sql中关键字的使用姿势

59822
来自专栏aoho求索

Mysql探索(一):B-Tree索引

MySQL是目前业界最为流行的关系型数据库之一,而索引的优化也是数据库性能优化的关键之一。所以,充分地了解MySQL索引有助于提升开发人员对MySQL数据库的使...

1011
来自专栏Albert陈凯

2018-07-20 oracle优化:避免全表扫描

例如:在City-State-Zip列创建了三列复合索引,那么仅对State列限定条件不能使用这个索引,因为State不是索引的主列。

1924
来自专栏文渊之博

探索SQL Server元数据(三):索引元数据

在第一篇中我介绍了如何访问元数据,元数据为什么在数据库里面,以及如何使用元数据。介绍了如何查出各种数据库对象的在数据库里面的名字。第二篇,我选择了触发器的主题,...

1561
来自专栏自由而无用的灵魂的碎碎念

分享:Oracle sql语句优化

最近做查询时,写的一条查询语句用了两个IN,导致tuexdo服务积压了不少,用户没骂就不错了。最后经过技术经理的点拨,sql语句性能提升了大约10倍,主要用了表...

1011
来自专栏极客慕白的成长之路

知识点、SQL语句学习及详细总结

1622

扫码关注云+社区