sql 列转行

行列互转，是一个常常遇到的需求。实现的方法，有case when方式和2005以后的内置pivot和unpivot方法来实现。sql

在读了技术内幕那一节后，虽然说这些解决方案早就用过了，却没有系统性的认识和总结过。为了加深认识，再总结一次。函数

行列互转，能够分为静态互转，即事先就知道要处理多少行（列）;动态互转,事先不知道处理多少行(列)。工具

工具/原料

• SQL SERVER 2008测试

方法/步骤

1. --建立测试环境xml

   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字符串

2. 行转列-静态方案：qt

    

   --行转列的静态方案一：CASE WHEN,兼容sql2000it

    

   select custid,io

   sum(case when YEAR(orderdate)=2002 then qty end) as [2002],date

   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

3. 行转列-动态方案：加入了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

4. 列转行：

    

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