行列互转，是一个经常遇到的需求。实现的方法，有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,兼容sql20