查看: 2385|回复: 0

[SQLServer] SQL行转列和列转行代码详解

发表于 2018-4-1 11:29:59

行列互转,是一个经常遇到的需求。实现的方法,有case when方式和2005之后的内置pivot和unpivot方法来实现。
在读了技术内幕那一节后,虽说这些解决方案早就用过了,却没有系统性的认识和总结过。为了加深认识,再总结一次。
行列互转,可以分为静态互转,即事先就知道要处理多少行(列);动态互转,事先不知道处理多少行(列)。

  1. --创建测试环境
  2. USE tempdb;
  3. GO
  4. IF OBJECT_ID('dbo.Orders') IS NOT NULL
  5. DROP TABLE dbo.Orders;
  6. GO
  7. CREATE TABLE dbo.Orders
  8. (
  9. orderid int NOT NULL PRIMARY KEY NONCLUSTERED,
  10. orderdate datetime NOT NULL,
  11. empid int NOT NULL,
  12. custid varchar(5) NOT NULL,
  13. qty int NOT NULL
  14. );
  15. CREATE UNIQUE CLUSTERED INDEX idx_orderdate_orderid
  16. ON dbo.Orders(orderdate, orderid);
  17. INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
  18. VALUES(30001, '20020802', 3, 'A', 10);
  19. INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
  20. VALUES(10001, '20021224', 1, 'A', 12);
  21. INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
  22. VALUES(10005, '20021224', 1, 'B', 20);
  23. INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
  24. VALUES(40001, '20030109', 4, 'A', 40);
  25. INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
  26. VALUES(10006, '20030118', 1, 'C', 14);
  27. INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
  28. VALUES(20001, '20030212', 2, 'B', 12);
  29. INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
  30. VALUES(40005, '20040212', 4, 'A', 10);
  31. INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
  32. VALUES(20002, '20040216', 2, 'C', 20);
  33. INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
  34. VALUES(30003, '20040418', 3, 'B', 15);
  35. INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
  36. VALUES(30004, '20020418', 3, 'C', 22);
  37. INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
  38. VALUES(30007, '20020907', 3, 'D', 30);
  39. GO
复制代码

行转列-静态方案:

  1. --行转列的静态方案一:CASE WHEN,兼容sql2000
  2. select custid,
  3. sum(case when YEAR(orderdate)=2002 then qty end) as [2002],
  4. sum(case when YEAR(orderdate)=2003 then qty end) as [2003],
  5. sum(case when YEAR(orderdate)=2004 then qty end) as [2004]
  6. from orders
  7. group by custid;
  8. GO
  9. --行转列的静态方案二:PIVOT,sql2005及以后版本
  10. select *
  11. from (select custid,YEAR(orderdate) as years,qty from orders) as ord
  12. pivot(sum(qty) for years in([2002],[2003],[2004]))as p
  13. GO
复制代码

行转列-动态方案:加入了xml处理和SQL注入预防判断

  1. --既然是用到了动态SQL,就有一个老话题:SQL注入。建一个注入性字符的判断函数。
  2. CREATE FUNCTION [dbo].[fn_CheckSQLInjection]
  3. (
  4. @Col nvarchar(4000)
  5. )
  6. RETURNS BIT --如果存在可能的注入字符返回true,反之返回false
  7. AS
  8. BEGIN
  9. DECLARE @result bit;
  10. IF
  11. UPPER(@Col) LIKE UPPER(N'%0x%')
  12. OR UPPER(@Col) LIKE UPPER(N'%;%')
  13. OR UPPER(@Col) LIKE UPPER(N'%''%')
  14. OR UPPER(@Col) LIKE UPPER(N'%--%')
  15. OR UPPER(@Col) LIKE UPPER(N'%/*%*/%')
  16. OR UPPER(@Col) LIKE UPPER(N'%EXEC%')
  17. OR UPPER(@Col) LIKE UPPER(N'%xp_%')
  18. OR UPPER(@Col) LIKE UPPER(N'%sp_%')
  19. OR UPPER(@Col) LIKE UPPER(N'%SELECT%')
  20. OR UPPER(@Col) LIKE UPPER(N'%INSERT%')
  21. OR UPPER(@Col) LIKE UPPER(N'%UPDATE%')
  22. OR UPPER(@Col) LIKE UPPER(N'%DELETE%')
  23. OR UPPER(@Col) LIKE UPPER(N'%TRUNCATE%')
  24. OR UPPER(@Col) LIKE UPPER(N'%CREATE%')
  25. OR UPPER(@Col) LIKE UPPER(N'%ALTER%')
  26. OR UPPER(@Col) LIKE UPPER(N'%DROP%')
  27. SET @result=1
  28. ELSE
  29. SET @result=0
  30. return @result
  31. END
  32. GO
  33. --行转列的动态方案一:CASE WHEN,兼容sql2000
  34. DECLARE @T TABLE (years INT NOT NULL PRIMARY KEY);
  35. INSERT INTO @T
  36. SELECT DISTINCT YEAR(orderdate) from orders;
  37. DECLARE @Y INT;
  38. SET @Y=(SELECT MIN(years) from @T);
  39. DECLARE @SQL NVARCHAR(4000)=N'';
  40. WHILE @Y IS NOT NULL
  41. BEGIN
  42. SET @SQL=@SQL+N',sum(case when YEAR(orderdate)='+CAST(@Y AS NVARCHAR(4)) +N' then qty end) as '+QUOTENAME(@Y);
  43. SET @Y=(SELECT MIN(years) from @T where years>@Y);
  44. END
  45. IF dbo.fn_CheckSQLInjection(@SQL)=0
  46. SET @SQL=N'SELECT custid'+@SQL+N' FROM orders group by custid'
  47. PRINT @SQL
  48. EXEC sp_executesql @SQL
  49. GO
  50. --行转列的动态方案二:PIVOT,sql2005及以后版本
  51. DECLARE @T TABLE (years INT NOT NULL PRIMARY KEY);
  52. INSERT INTO @T
  53. SELECT DISTINCT YEAR(orderdate) from orders;
  54. DECLARE @Y INT;
  55. SET @Y=(SELECT MIN(years) from @T);
  56. DECLARE @SQL NVARCHAR(4000)=N'';
  57. --这里使用了xml处理来处理类组字符串
  58. SET @SQL=STUFF((SELECT N','+QUOTENAME(years) FROM @T
  59. FOR XML PATH('')),1,1,N'');
  60. IF dbo.fn_CheckSQLInjection(@SQL)=0
  61. SET @SQL=N'select * from (select DISTINCT custid,YEAR(orderdate) as years,qty from orders) as ord
  62. pivot(sum(qty) for years in('+@SQL+N'))as p';
  63. PRINT @SQL;
  64. EXEC SP_EXECUTESQL @SQL;
  65. GO
复制代码

列转行:

  1. --列转行的静态方案:UNPIVOT,sql2005及以后版本
  2. SELECT * FROM dbo.pvtCustOrders
  3. SELECT custid,years,qty
  4. from dbo.pvtCustOrders
  5. unpivot(qty for years in([2002],[2003],[2004]))as up
  6. GO
  7. --列转行的动态方案:UNPIVOT,sql2005及以后版本
  8. --因为行是动态所以这里就从INFORMATION_SCHEMA.COLUMNS视图中获取列来构造行,同样也使用了XML处理。
  9. DECLARE @SQL NVARCHAR(4000)=N'';
  10. SET @SQL=STUFF((SELECT N','+QUOTENAME(COLUMN_NAME ) FROM INFORMATION_SCHEMA.COLUMNS
  11. WHERE ORDINAL_POSITION>1 AND TABLE_NAME='PvtCustOrders'
  12. FOR XML PATH('')),1,1,N'')
  13. SET @SQL=N'SELECT custid,years,qty
  14. from dbo.pvtCustOrders
  15. unpivot(qty for years in('+@SQL+'))as up';
  16. PRINT @SQL;
  17. EXEC SP_EXECUTESQL @SQL;
复制代码

总结

以上就是本文关于SQL行转列和列转行代码详解的全部内容,希望对大家有所帮助。感兴趣的朋友可以继续参阅本站:Mysql中FIND_IN_SET()和IN区别简析、浅谈sqlserver下float的不确定性、MYSQL子查询和嵌套查询优化实例解析等,有什么问题可以随时留言,小编会及时回复大家的。感谢朋友们对程序员之家网站的支持!



回复

使用道具 举报