SQL SERVER 行转列和列转行

操作方法

• 01

  --创建测试环境USE tempdb;GOIF OBJECT_ID('dbo.Orders') IS NOT NULL  DROP TABLE dbo.Orders;GOCREATE 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

• 02

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

• 03

  行转列-动态方案：加入了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=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 ordpivot(sum(qty) for years in('+@SQL+N'))as p'; PRINT @SQL; EXEC SP_EXECUTESQL @SQL; GO

• 04

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