行列互转,是一个经常遇到的需求。实现的方法,有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)= 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 @ 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 @ 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子查询和嵌套查询优化实例解析等,有什么问题可以随时留言,小编会及时回复大家的。感谢朋友们对沃谷博客网站的支持!

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注