SQL行转列和列转行代码详解
行列互转，是一个经常遇到的需求。实现的方法，有casewhen方式和2005之后的内置pivot和unpivot方法来实现。
在读了技术内幕那一节后，虽说这些解决方案早就用过了，却没有系统性的认识和总结过。为了加深认识，再总结一次。
行列互转，可以分为静态互转，即事先就知道要处理多少行（列）;动态互转,事先不知道处理多少行(列)。
--创建测试环境
USEtempdb;
GO
IFOBJECT_ID('dbo.Orders')ISNOTNULL
DROPTABLEdbo.Orders;
GO
CREATETABLEdbo.Orders
(
orderidintNOTNULLPRIMARYKEYNONCLUSTERED,
orderdatedatetimeNOTNULL,
empidintNOTNULL,
custidvarchar(5)NOTNULL,
qtyintNOTNULL
);
CREATEUNIQUECLUSTEREDINDEXidx_orderdate_orderid
ONdbo.Orders(orderdate,orderid);
INSERTINTOdbo.Orders(orderid,orderdate,empid,custid,qty)
VALUES(30001,'20020802',3,'A',10);
INSERTINTOdbo.Orders(orderid,orderdate,empid,custid,qty)
VALUES(10001,'20021224',1,'A',12);
INSERTINTOdbo.Orders(orderid,orderdate,empid,custid,qty)
VALUES(10005,'20021224',1,'B',20);
INSERTINTOdbo.Orders(orderid,orderdate,empid,custid,qty)
VALUES(40001,'20030109',4,'A',40);
INSERTINTOdbo.Orders(orderid,orderdate,empid,custid,qty)
VALUES(10006,'20030118',1,'C',14);
INSERTINTOdbo.Orders(orderid,orderdate,empid,custid,qty)
VALUES(20001,'20030212',2,'B',12);
INSERTINTOdbo.Orders(orderid,orderdate,empid,custid,qty)
VALUES(40005,'20040212',4,'A',10);
INSERTINTOdbo.Orders(orderid,orderdate,empid,custid,qty)
VALUES(20002,'20040216',2,'C',20);
INSERTINTOdbo.Orders(orderid,orderdate,empid,custid,qty)
VALUES(30003,'20040418',3,'B',15);
INSERTINTOdbo.Orders(orderid,orderdate,empid,custid,qty)
VALUES(30004,'20020418',3,'C',22);
INSERTINTOdbo.Orders(orderid,orderdate,empid,custid,qty)
VALUES(30007,'20020907',3,'D',30);
GO
行转列-静态方案：
--行转列的静态方案一：CASEWHEN,兼容sql2000 selectcustid, sum(casewhenYEAR(orderdate)=2002thenqtyend)as[2002], sum(casewhenYEAR(orderdate)=2003thenqtyend)as[2003], sum(casewhenYEAR(orderdate)=2004thenqtyend)as[2004] fromorders groupbycustid; GO --行转列的静态方案二：PIVOT,sql2005及以后版本 select* from(selectcustid,YEAR(orderdate)asyears,qtyfromorders)asord pivot(sum(qty)foryearsin([2002],[2003],[2004]))asp GO
行转列-动态方案：加入了xml处理和SQL注入预防判断
--既然是用到了动态SQL,就有一个老话题:SQL注入。建一个注入性字符的判断函数。
CREATEFUNCTION[dbo].[fn_CheckSQLInjection]
(
@Colnvarchar(4000)
)
RETURNSBIT--如果存在可能的注入字符返回true,反之返回false
AS
BEGIN
DECLARE@resultbit;
IF
UPPER(@Col)LIKEUPPER(N'%0x%')
ORUPPER(@Col)LIKEUPPER(N'%;%')
ORUPPER(@Col)LIKEUPPER(N'%''%')
ORUPPER(@Col)LIKEUPPER(N'%--%')
ORUPPER(@Col)LIKEUPPER(N'%/*%*/%')
ORUPPER(@Col)LIKEUPPER(N'%EXEC%')
ORUPPER(@Col)LIKEUPPER(N'%xp_%')
ORUPPER(@Col)LIKEUPPER(N'%sp_%')
ORUPPER(@Col)LIKEUPPER(N'%SELECT%')
ORUPPER(@Col)LIKEUPPER(N'%INSERT%')
ORUPPER(@Col)LIKEUPPER(N'%UPDATE%')
ORUPPER(@Col)LIKEUPPER(N'%DELETE%')
ORUPPER(@Col)LIKEUPPER(N'%TRUNCATE%')
ORUPPER(@Col)LIKEUPPER(N'%CREATE%')
ORUPPER(@Col)LIKEUPPER(N'%ALTER%')
ORUPPER(@Col)LIKEUPPER(N'%DROP%')
SET@result=1
ELSE
SET@result=0
return@result
END
GO
--行转列的动态方案一：CASEWHEN,兼容sql2000
DECLARE@TTABLE(yearsINTNOTNULLPRIMARYKEY);
INSERTINTO@T
SELECTDISTINCTYEAR(orderdate)fromorders;
DECLARE@YINT;
SET@Y=(SELECTMIN(years)from@T);
DECLARE@SQLNVARCHAR(4000)=N'';
WHILE@YISNOTNULL
BEGIN
SET@SQL=@SQL+N',sum(casewhenYEAR(orderdate)='+CAST(@YASNVARCHAR(4))+N'thenqtyend)as'+QUOTENAME(@Y);
SET@Y=(SELECTMIN(years)from@Twhereyears>@Y);
END
IFdbo.fn_CheckSQLInjection(@SQL)=0
SET@SQL=N'SELECTcustid'+@SQL+N'FROMordersgroupbycustid'
PRINT@SQL
EXECsp_executesql@SQL
GO
--行转列的动态方案二：PIVOT,sql2005及以后版本
DECLARE@TTABLE(yearsINTNOTNULLPRIMARYKEY);
INSERTINTO@T
SELECTDISTINCTYEAR(orderdate)fromorders;
DECLARE@YINT;
SET@Y=(SELECTMIN(years)from@T);
DECLARE@SQLNVARCHAR(4000)=N'';
--这里使用了xml处理来处理类组字符串
SET@SQL=STUFF((SELECTN','+QUOTENAME(years)FROM@T
FORXMLPATH('')),1,1,N'');
IFdbo.fn_CheckSQLInjection(@SQL)=0
SET@SQL=N'select*from(selectDISTINCTcustid,YEAR(orderdate)asyears,qtyfromorders)asord
pivot(sum(qty)foryearsin('+@SQL+N'))asp';
PRINT@SQL;
EXECSP_EXECUTESQL@SQL;
GO
列转行：
--列转行的静态方案：UNPIVOT,sql2005及以后版本
SELECT*FROMdbo.pvtCustOrders
SELECTcustid,years,qty
fromdbo.pvtCustOrders
unpivot(qtyforyearsin([2002],[2003],[2004]))asup
GO
--列转行的动态方案：UNPIVOT,sql2005及以后版本
--因为行是动态所以这里就从INFORMATION_SCHEMA.COLUMNS视图中获取列来构造行，同样也使用了XML处理。
DECLARE@SQLNVARCHAR(4000)=N'';
SET@SQL=STUFF((SELECTN','+QUOTENAME(COLUMN_NAME)FROMINFORMATION_SCHEMA.COLUMNS
WHEREORDINAL_POSITION>1ANDTABLE_NAME='PvtCustOrders'
FORXMLPATH('')),1,1,N'')
SET@SQL=N'SELECTcustid,years,qty
fromdbo.pvtCustOrders
unpivot(qtyforyearsin('+@SQL+'))asup';
PRINT@SQL;
EXECSP_EXECUTESQL@SQL;
总结
以上就是本文关于SQL行转列和列转行代码详解的全部内容，希望对大家有所帮助。感兴趣的朋友可以继续参阅本站：Mysql中FIND_IN_SET()和IN区别简析、浅谈sqlserver下float的不确定性、MYSQL子查询和嵌套查询优化实例解析等，有什么问题可以随时留言，小编会及时回复大家的。感谢朋友们对毛票票网站的支持！