SQL如何实现行转列和列转行
发表于:2024-09-22 作者:千家信息网编辑
千家信息网最后更新 2024年09月22日,这篇文章给大家分享的是有关SQL如何实现行转列和列转行的内容。小编觉得挺实用的,因此分享给大家做个参考,一起跟随小编过来看看吧。行列互转,是一个经常遇到的需求。实现的方法,有case when方式和2
千家信息网最后更新 2024年09月22日SQL如何实现行转列和列转行
这篇文章给大家分享的是有关SQL如何实现行转列和列转行的内容。小编觉得挺实用的,因此分享给大家做个参考,一起跟随小编过来看看吧。
行列互转,是一个经常遇到的需求。实现的方法,有case when方式和2005之后的内置pivot和unpivot方法来实现。
在读了技术内幕那一节后,虽说这些解决方案早就用过了,却没有系统性的认识和总结过。为了加深认识,再总结一次。
行列互转,可以分为静态互转,即事先就知道要处理多少行(列);动态互转,事先不知道处理多少行(列)。
--创建测试环境USE tempdb;GOIF OBJECT_ID('dbo.Orders') IS NOT NULL DROP TABLE dbo.Orders;GOCREATE 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,兼容sql2000select 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 ordersgroup by custid;GO--行转列的静态方案二:PIVOT,sql2005及以后版本select *from (select custid,YEAR(orderdate) as years,qty from orders) as ordpivot(sum(qty) for years in([2002],[2003],[2004]))as pGO
行转列-动态方案:加入了xml处理和SQL注入预防判断
--既然是用到了动态SQL,就有一个老话题:SQL注入。建一个注入性字符的判断函数。CREATE FUNCTION [dbo].[fn_CheckSQLInjection]( @Col nvarchar(4000))RETURNS BIT --如果存在可能的注入字符返回true,反之返回falseASBEGINDECLARE @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 @resultENDGO--行转列的动态方案一:CASE WHEN,兼容sql2000DECLARE @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 NULLBEGIN 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);ENDIF dbo.fn_CheckSQLInjection(@SQL)=0SET @SQL=N'SELECT custid'+@SQL+N' FROM orders group by custid'PRINT @SQLEXEC sp_executesql @SQLGO--行转列的动态方案二: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)=0SET @SQL=N'select * from (select DISTINCT custid,YEAR(orderdate) as years,qty from orders) as ordpivot(sum(qty) for years in('+@SQL+N'))as p';PRINT @SQL;EXEC SP_EXECUTESQL @SQL;GO
列转行:
--列转行的静态方案:UNPIVOT,sql2005及以后版本SELECT * FROM dbo.pvtCustOrdersSELECT custid,years,qtyfrom dbo.pvtCustOrdersunpivot(qty for years in([2002],[2003],[2004]))as upGO--列转行的动态方案:UNPIVOT,sql2005及以后版本--因为行是动态所以这里就从INFORMATION_SCHEMA.COLUMNS视图中获取列来构造行,同样也使用了XML处理。DECLARE @SQL NVARCHAR(4000)=N'';SET @SQL=STUFF((SELECT N','+QUOTENAME(COLUMN_NAME ) FROM INFORMATION_SCHEMA.COLUMNSWHERE 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如何实现行转列和列转行"这篇文章就分享到这里了,希望以上内容可以对大家有一定的帮助,让大家可以学到更多知识,如果觉得文章不错,可以把它分享出去让更多的人看到吧!
方案
动态
处理
静态
版本
字符
内容
方法
更多
篇文章
行列
不错
实用
内幕
函数
字符串
技术
文章
方式
环境
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
软件开发系统小程序
十进制网络安全地址
数据库执行过程命令
软件开发者会留下开发姓名吗
linux数据库启动命令
服务器主机出厂时间
互联网科技公司的名字大全
数据库设计作业参考答案
软件开发必须具备的
rfid服务器都处理了哪些信息
徐州手机软件开发管理
苏州电商软件开发哪家正规
网络安全知识在哪里学习
文献型信息检索的主要数据库
ejs 数据库
云服务器搭建传奇视频
惠州软件开发费用是多少
挂赚宝网络技术有限公司官网
钦州民宿软件开发
网络安全伴我行征文比赛500字
河南有哪些软件开发公司
流花湖服务器
fpt数据库
无锡运营软件开发怎么样
网络安全的演讲稿四年级
软件开发属于什么类型企业
服务器主机用什么系统
网络安全敏感国家 朝鲜
数据库的端口号的使用
天津专业服务器机柜云空间