SQL行转列和列转行

转载

mb5fdcad8719a20 2016-12-13 13:18:00

文章标签 sql 行转列 xml 列转行 sql注入 文章分类 代码人生

行列互转，是一个经常遇到的需求。实现的方法，有case when方式和2005之后的内置pivot和unpivot方法来实现。

在读了技术内幕那一节后，虽说这些解决方案早就用过了，却没有系统性的认识和总结过。为了加深认识，再总结一次。

行列互转，可以分为静态互转，即事先就知道要处理多少行（列）;动态互转,事先不知道处理多少行(列)。

SQL行转列和列转行_sql注入

--创建测试环境

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

SQL行转列和列转行_sql_02

行转列-静态方案：

SQL行转列和列转行_xml_03

--行转列的静态方案一：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

SQL行转列和列转行_sql_04

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

SQL行转列和列转行_sql注入_05

--既然是用到了动态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

SQL行转列和列转行_列转行_06

列转行：

SQL行转列和列转行_sql注入_07

--列转行的静态方案：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行转列和列转行_行转列_08

行列互转，是一个经常遇到的需求。实现的方法，有case when方式和2005之后的内置pivot和unpivot方法来实现。

在读了技术内幕那一节后，虽说这些解决方案早就用过了，却没有系统性的认识和总结过。为了加深认识，再总结一次。

行列互转，可以分为静态互转，即事先就知道要处理多少行（列）;动态互转,事先不知道处理多少行(列)。

SQL行转列和列转行_列转行_09

--创建测试环境

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

SQL行转列和列转行_行转列_10

行转列-静态方案：

SQL行转列和列转行_列转行_11

--行转列的静态方案一：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

SQL行转列和列转行_sql注入_12

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

SQL行转列和列转行_列转行_13

--既然是用到了动态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

SQL行转列和列转行_xml_14

列转行：

SQL行转列和列转行_sql注入_15

--列转行的静态方案：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行转列和列转行_sql_16

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：dump_stack调用过程【原创】

下一篇：精简JRE的思路初探

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

SQL行转列和列转行

SQL行转列和列转行

51CTO博客