时间:2021-05-23
行列互转,是一个经常遇到的需求。实现的方法,有case when方式和2005之后的内置pivot和unpivot方法来实现。
在读了技术内幕那一节后,虽说这些解决方案早就用过了,却没有系统性的认识和总结过。为了加深认识,再总结一次。
行列互转,可以分为静态互转,即事先就知道要处理多少行(列);动态互转,事先不知道处理多少行(列)。
行转列-静态方案:
--行转列的静态方案一: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行转列和列转行代码详解的全部内容,希望对大家有所帮助。感兴趣的朋友可以继续参阅本站:Mysql中FIND_IN_SET()和IN区别简析、浅谈sqlserver下float的不确定性、MYSQL子查询和嵌套查询优化实例解析等,有什么问题可以随时留言,小编会及时回复大家的。感谢朋友们对网站的支持!
声明:本页内容来源网络,仅供用户参考;我单位不保证亦不表示资料全面及准确无误,也不保证亦不表示这些资料为最新信息,如因任何原因,本网内容或者用户因倚赖本网内容造成任何损失或损害,我单位将不会负任何法律责任。如涉及版权问题,请提交至online#300.cn邮箱联系删除。
mysql行转列、列转行语句不难,不做多余解释了,看语句时,从内往外一句一句剖析行转列有如图所示的表,现在希望查询的结果将行转成列建表语句如下:CREATETA
复制代码代码如下:--行转列问题--建立測試環境CreateTableTEST(DATESVarchar(6),EMPNOVarchar(5),STYPEVar
行转列一张表查询结果为--行转列selectyears,(selectamountfromTb_AmountasAwheremonth=1andA.years=
什么是自动映射?介绍自动映射之前先看一下手动映射,如下:注意上面的resultMap元素中有4行配置,如下:这4行代码用于配置sql结果的列和OrderMode
复制代码代码如下://////DataTable行转列//////需要转换的表///转换表表头对应旧表字段(小写)///publicstaticDataTabl