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=NSELECT 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=Nselect * 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=NSELECT custid,years,qty
         from dbo.pvtCustOrders
         unpivot(qty for years in(+@SQL+))as up;
PRINT @SQL;
EXEC SP_EXECUTESQL @SQL;

转自:http://www.cnblogs.com/yexinw/archive/2012/08/31/2665171.html

sql行转列和列转行(转),古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。