sql语句行转列怎么设置(SQL行转列和列转行代码详解)
类别:数据库 浏览量:1787
时间:2021-10-24 10:39:41 sql语句行转列怎么设置
SQL行转列和列转行代码详解行列互转,是一个经常遇到的需求。实现的方法,有case when方式和2005之后的内置pivot和unpivot方法来实现。
在读了技术内幕那一节后,虽说这些解决方案早就用过了,却没有系统性的认识和总结过。为了加深认识,再总结一次。
行列互转,可以分为静态互转,即事先就知道要处理多少行(列);动态互转,事先不知道处理多少行(列)。
--创建测试环境 USE tempdb; GO IF OBJECT_ID('dbo.Orders') IS NOT NULL DROP TABLE dbo.Orders; GO CREATE TABLE dbo.Orders ( orderid int NOT NULL PRIMARY KEY NONCLUSTERED, orderdate datetime NOT NULL, empid int NOT NULL, custid varchar(5) NOT NULL, qty int NOT NULL ); CREATE UNIQUE CLUSTERED INDEX idx_orderdate_orderid ON dbo.Orders(orderdate, orderid); INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty) VALUES(30001, '20020802', 3, 'A', 10); INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty) VALUES(10001, '20021224', 1, 'A', 12); INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty) VALUES(10005, '20021224', 1, 'B', 20); INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty) VALUES(40001, '20030109', 4, 'A', 40); INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty) VALUES(10006, '20030118', 1, 'C', 14); INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty) VALUES(20001, '20030212', 2, 'B', 12); INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty) VALUES(40005, '20040212', 4, 'A', 10); INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty) VALUES(20002, '20040216', 2, 'C', 20); INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty) VALUES(30003, '20040418', 3, 'B', 15); INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty) VALUES(30004, '20020418', 3, 'C', 22); INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty) VALUES(30007, '20020907', 3, 'D', 30); GO
行转列-静态方案:
--行转列的静态方案一:CASE WHEN,兼容sql2000 select custid, sum(case when YEAR(orderdate)=2002 then qty end) as [2002], sum(case when YEAR(orderdate)=2003 then qty end) as [2003], sum(case when YEAR(orderdate)=2004 then qty end) as [2004] from orders group by custid; GO --行转列的静态方案二:PIVOT,sql2005及以后版本 select * from (select custid,YEAR(orderdate) as years,qty from orders) as ord pivot(sum(qty) for years in([2002],[2003],[2004]))as p GO
行转列-动态方案:加入了xml处理和SQL注入预防判断
--既然是用到了动态SQL,就有一个老话题:SQL注入。建一个注入性字符的判断函数。 CREATE FUNCTION [dbo].[fn_CheckSQLInjection] ( @Col nvarchar(4000) ) RETURNS BIT --如果存在可能的注入字符返回true,反之返回false AS BEGIN DECLARE @result bit; IF UPPER(@Col) LIKE UPPER(N'%0x%') OR UPPER(@Col) LIKE UPPER(N'%;%') OR UPPER(@Col) LIKE UPPER(N'%''%') OR UPPER(@Col) LIKE UPPER(N'%--%') OR UPPER(@Col) LIKE UPPER(N'%/*%*/%') OR UPPER(@Col) LIKE UPPER(N'%EXEC%') OR UPPER(@Col) LIKE UPPER(N'%xp_%') OR UPPER(@Col) LIKE UPPER(N'%sp_%') OR UPPER(@Col) LIKE UPPER(N'%SELECT%') OR UPPER(@Col) LIKE UPPER(N'%INSERT%') OR UPPER(@Col) LIKE UPPER(N'%UPDATE%') OR UPPER(@Col) LIKE UPPER(N'%DELETE%') OR UPPER(@Col) LIKE UPPER(N'%TRUNCATE%') OR UPPER(@Col) LIKE UPPER(N'%CREATE%') OR UPPER(@Col) LIKE UPPER(N'%ALTER%') OR UPPER(@Col) LIKE UPPER(N'%DROP%') SET @result=1 ELSE SET @result=0 return @result END GO --行转列的动态方案一:CASE WHEN,兼容sql2000 DECLARE @T TABLE (years INT NOT NULL PRIMARY KEY); INSERT INTO @T SELECT DISTINCT YEAR(orderdate) from orders; DECLARE @Y INT; SET @Y=(SELECT MIN(years) from @T); DECLARE @SQL NVARCHAR(4000)=N''; WHILE @Y IS NOT NULL BEGIN SET @SQL=@SQL+N',sum(case when YEAR(orderdate)='+CAST(@Y AS NVARCHAR(4)) +N' then qty end) as '+QUOTENAME(@Y); SET @Y=(SELECT MIN(years) from @T where years>@Y); END IF dbo.fn_CheckSQLInjection(@SQL)=0 SET @SQL=N'SELECT custid'+@SQL+N' FROM orders group by custid' PRINT @SQL EXEC sp_executesql @SQL GO --行转列的动态方案二:PIVOT,sql2005及以后版本 DECLARE @T TABLE (years INT NOT NULL PRIMARY KEY); INSERT INTO @T SELECT DISTINCT YEAR(orderdate) from orders; DECLARE @Y INT; SET @Y=(SELECT MIN(years) from @T); DECLARE @SQL NVARCHAR(4000)=N''; --这里使用了xml处理来处理类组字符串 SET @SQL=STUFF((SELECT N','+QUOTENAME(years) FROM @T FOR XML PATH('')),1,1,N''); IF dbo.fn_CheckSQLInjection(@SQL)=0 SET @SQL=N'select * from (select DISTINCT custid,YEAR(orderdate) as years,qty from orders) as ord pivot(sum(qty) for years in('+@SQL+N'))as p'; PRINT @SQL; EXEC SP_EXECUTESQL @SQL; GO
列转行:
--列转行的静态方案:UNPIVOT,sql2005及以后版本 SELECT * FROM dbo.pvtCustOrders SELECT custid,years,qty from dbo.pvtCustOrders unpivot(qty for years in([2002],[2003],[2004]))as up GO --列转行的动态方案:UNPIVOT,sql2005及以后版本 --因为行是动态所以这里就从INFORMATION_SCHEMA.COLUMNS视图中获取列来构造行,同样也使用了XML处理。 DECLARE @SQL NVARCHAR(4000)=N''; SET @SQL=STUFF((SELECT N','+QUOTENAME(COLUMN_NAME ) FROM INFORMATION_SCHEMA.COLUMNS WHERE ORDINAL_POSITION>1 AND TABLE_NAME='PvtCustOrders' FOR XML PATH('')),1,1,N'') SET @SQL=N'SELECT custid,years,qty from dbo.pvtCustOrders unpivot(qty for years in('+@SQL+'))as up'; PRINT @SQL; EXEC SP_EXECUTESQL @SQL;
总结
以上就是本文关于SQL行转列和列转行代码详解的全部内容,希望对大家有所帮助。感兴趣的朋友可以继续参阅本站:Mysql中FIND_IN_SET()和IN区别简析、浅谈sqlserver下float的不确定性、MYSQL子查询和嵌套查询优化实例解析等,有什么问题可以随时留言,小编会及时回复大家的。感谢朋友们对开心学习网网站的支持!
您可能感兴趣
- sql提交事务代码(SQL SERVER提交事务回滚机制)
- rename重命名mysql表(MySQL 重命名表的操作方法及注意事项)
- mysql删除数据库的命令(MySQL 线上数据库清理数据的方法)
- SQLServer数据库中开启CDC导致事务日志空间被占满的原因(SQLServer数据库中开启CDC导致事务日志空间被占满的原因)
- Sql如何删除重复记录
- sqlserver分表后如何查询(SQL Server中row_number分页查询的用法详解)
- Sql Server判断函数、存储过程等是否存在
- sql中exists怎么用(SQL中Exists的用法)
- SQL SERVER书签查找
- mysql多表连接优化(浅谈Mysql多表连接查询的执行细节)
- SQLServer设置客户端使用IP地址登录的图文详解(SQLServer设置客户端使用IP地址登录的图文详解)
- sqlserver创建表结构时添加约束(SQL Server 通过with as方法查询树型结构)
- mysql新增字段并添加备注(mysql添加备注信息的实现)
- sqlserver附加数据库时出错(SQL Server数据库附加失败的解决办法)
- mysql事务级别设置(mysql在项目中怎么选事务隔离级别)
- spark sql常用操作(Spark SQL数据加载和保存实例讲解)
- 哪版孙悟空最萌 黄渤躺萌了(哪版孙悟空最萌)
- 融入小人物的喜怒哀乐,黄渤饰演的角色为什么让人观看时欲罢不能(融入小人物的喜怒哀乐)
- 《极限挑战》深访都市夜归人,夜间打工者体验,黄磊录完憔悴了(极限挑战深访都市夜归人)
- Google 推出了一个游戏生成器,让不会编程的你也能自己设计游戏(推出了一个游戏生成器)
- 二胎家庭老大爱闹情绪,用这招很有效(二胎家庭老大爱闹情绪)
- 一个30岁男人外遇失败的全过程(一个30岁男人外遇失败的全过程)
热门推荐
- 所有聚合函数mysql教程(Mysql 聚合函数嵌套使用操作)
- 织梦tag标签怎样设置(织梦dede修改关键字长度的方法)
- python开发网站github(使用 Python 玩转 GitHub 的贡献板推荐)
- mysql快速备份数据(如何使用Maxwell实时同步mysql数据)
- mysql对null值如何理解(MySQL为Null会导致5个问题个个致命)
- 虚拟主机是搭建网站吗(电商网站选择虚拟主机的教程)
- WinForm窗体间如何传值
- 设置404错误页面的几个建议
- 阿里云服务器清除密码(阿里云服务器忘记密码怎么办?阿里云ECS密码重置图文教程)
- css样式表中border在哪(CSS中使用border来创建三角形的基本方法讲解)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9