这篇文章给大家分享的是有关SQL如何实现行转列和列转行的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。

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

--创建测试环境USEtempdb;GOIFOBJECT_ID('dbo.Orders')ISNOTNULLDROPTABLEdbo.Orders;GOCREATETABLEdbo.Orders(orderidintNOTNULLPRIMARYKEYNONCLUSTERED,orderdatedatetimeNOTNULL,empidintNOTNULL,custidvarchar(5)NOTNULL,qtyintNOTNULL);CREATEUNIQUECLUSTEREDINDEXidx_orderdate_orderidONdbo.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,兼容sql2000selectcustid,sum(casewhenYEAR(orderdate)=2002thenqtyend)as[2002],sum(casewhenYEAR(orderdate)=2003thenqtyend)as[2003],sum(casewhenYEAR(orderdate)=2004thenqtyend)as[2004]fromordersgroupbycustid;GO--行转列的静态方案二：PIVOT,sql2005及以后版本select*from(selectcustid,YEAR(orderdate)asyears,qtyfromorders)asordpivot(sum(qty)foryearsin([2002],[2003],[2004]))aspGO

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

--既然是用到了动态SQL,就有一个老话题:SQL注入。建一个注入性字符的判断函数。CREATEFUNCTION[dbo].[fn_CheckSQLInjection](@Colnvarchar(4000))RETURNSBIT--如果存在可能的注入字符返回true,反之返回falseASBEGINDECLARE@resultbit;IFUPPER(@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=1ELSESET@result=0return@resultENDGO--行转列的动态方案一：CASEWHEN,兼容sql2000DECLARE@TTABLE(yearsINTNOTNULLPRIMARYKEY);INSERTINTO@TSELECTDISTINCTYEAR(orderdate)fromorders;DECLARE@YINT;SET@Y=(SELECTMIN(years)from@T);DECLARE@SQLNVARCHAR(4000)=N'';WHILE@YISNOTNULLBEGINSET@SQL=@SQL+N',sum(casewhenYEAR(orderdate)='+CAST(@YASNVARCHAR(4))+N'thenqtyend)as'+QUOTENAME(@Y);SET@Y=(SELECTMIN(years)from@Twhereyears>@Y);ENDIFdbo.fn_CheckSQLInjection(@SQL)=0SET@SQL=N'SELECTcustid'+@SQL+N'FROMordersgroupbycustid'PRINT@SQLEXECsp_executesql@SQLGO--行转列的动态方案二：PIVOT,sql2005及以后版本DECLARE@TTABLE(yearsINTNOTNULLPRIMARYKEY);INSERTINTO@TSELECTDISTINCTYEAR(orderdate)fromorders;DECLARE@YINT;SET@Y=(SELECTMIN(years)from@T);DECLARE@SQLNVARCHAR(4000)=N'';--这里使用了xml处理来处理类组字符串SET@SQL=STUFF((SELECTN','+QUOTENAME(years)FROM@TFORXMLPATH('')),1,1,N'');IFdbo.fn_CheckSQLInjection(@SQL)=0SET@SQL=N'select*from(selectDISTINCTcustid,YEAR(orderdate)asyears,qtyfromorders)asordpivot(sum(qty)foryearsin('+@SQL+N'))asp';PRINT@SQL;EXECSP_EXECUTESQL@SQL;GO

列转行：

--列转行的静态方案：UNPIVOT,sql2005及以后版本SELECT*FROMdbo.pvtCustOrdersSELECTcustid,years,qtyfromdbo.pvtCustOrdersunpivot(qtyforyearsin([2002],[2003],[2004]))asupGO--列转行的动态方案：UNPIVOT,sql2005及以后版本--因为行是动态所以这里就从INFORMATION_SCHEMA.COLUMNS视图中获取列来构造行，同样也使用了XML处理。DECLARE@SQLNVARCHAR(4000)=N'';SET@SQL=STUFF((SELECTN','+QUOTENAME(COLUMN_NAME)FROMINFORMATION_SCHEMA.COLUMNSWHEREORDINAL_POSITION>1ANDTABLE_NAME='PvtCustOrders'FORXMLPATH('')),1,1,N'')SET@SQL=N'SELECTcustid,years,qtyfromdbo.pvtCustOrdersunpivot(qtyforyearsin('+@SQL+'))asup';PRINT@SQL;EXECSP_EXECUTESQL@SQL;

感谢各位的阅读！关于“SQL如何实现行转列和列转行”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！