今天小编跟大家讲解下有关“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行列互转的处理方式有什么，具体怎样实现”的相关知识，感谢各位的阅读，想要掌握这篇文章的知识点还需要大家自己动手实践使用过才能领会，如果想了解更多相关内容的文章，欢迎关注群英网络，小编每天都会为大家更新不同的知识。

免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：mmqy2019@163.com进行举报，并提供相关证据，查实之后，将立刻删除涉嫌侵权内容。

猜你喜欢

• SQLServer数据库的主键和唯一约束存有何差别

  这篇文章主要介绍了SQLServer主键和唯一约束的区别,需要的朋友可以参考下

• SQL HAVING子句的语法是什么，如何使用

  SQL ​HAVING​ 子句使你能够指定过滤条件，从而控制查询结果中哪些组可以出现在最终结果里面。WHERE​子句对被选择的列施加条件，而 ​HAVING​子句则对 ​GROUP BY​ 子句所产生的组施加条件。

• SQL Server中执行计划缓存要考虑什么，常见问题有哪些

  这篇文章主要介绍了浅析SQL Server中的执行计划缓存（下）的相关资料,需要的朋友可以参考下

• Mybatis的sql注释怎样写，怎么使用

  这篇文章主要介绍了关于Mybatis的sql注释问题,本文通过示例代码给大家介绍的非常详细，对大家的学习或工作具有一定的参考借鉴价值，需要的朋友可以参考下

• SQL时间的模糊查询方式有几种，适合什么场景

  今天用time Like '2008-06-01%'语句来查询该天的所有数据，被提示语句错误。查了一下才发现该模糊查询只能用于String类型的字段。 自己也查阅了一些资料。关于时间的模糊查询有以下三种方法： 1.Convert转成Stri