sql server STUFF行专列 sql列转行最简单的方法

行转列，列转行是我们在开发过程中经常碰到的问题。行转列一般通过CASE WHEN 语句来实现，也可以通过 SQL SERVER 2005 新增的运算符PIVOT来实现。 用传统的方法，比较好理解。层次清晰，而且比较习惯。 但是PIVOT 、 UNPIVOT提供的语法比一系列复杂的SELECT...CASE 语句中所指定的语法更简单、更具可读性。下面我们通过几个简单的例子来介绍一下列转行、行转列问题。

我们首先先通过一个老生常谈的例子，学生成绩表(下面简化了些)来形象了解下行转列

CREATE  
  TABLE [StudentScores]( [UserName] NVARCHAR(20), --学生姓名 [Subject] NVARCHAR(30), --科目 [Score]  
  FLOAT, --成绩) INSERT  
  INTO [StudentScores]  
  SELECT  
  'Nick',  
  '语文', 80 INSERT  
  INTO [StudentScores]  
  SELECT  
  'Nick',  
  '数学', 90 INSERT  
  INTO [StudentScores]  
  SELECT  
  'Nick',  
  '英语', 70 INSERT  
  INTO [StudentScores]  
  SELECT  
  'Nick',  
  '生物', 85 INSERT  
  INTO [StudentScores]  
  SELECT  
  'Kent',  
  '语文', 80 INSERT  
  INTO [StudentScores]  
  SELECT  
  'Kent',  
  '数学', 90 INSERT  
  INTO [StudentScores]  
  SELECT  
  'Kent',  
  '英语', 70 INSERT  
  INTO [StudentScores]  
  SELECT  
  'Kent',  
  '生物', 85

如果我想知道每位学生的每科成绩，而且每个学生的全部成绩排成一行，这样方便我查看、统计，导出数据

SELECT UserName,  
  MAX( 
  CASE Subject  
  WHEN  
  '语文'  
  THEN Score  
  ELSE 0  
  END)  
  AS  
  '语文',  
  MAX( 
  CASE Subject  
  WHEN  
  '数学'  
  THEN Score  
  ELSE 0  
  END)  
  AS  
  '数学',  
  MAX( 
  CASE Subject  
  WHEN  
  '英语'  
  THEN Score  
  ELSE 0  
  END)  
  AS  
  '英语',  
  MAX( 
  CASE Subject  
  WHEN  
  '生物'  
  THEN Score  
  ELSE 0  
  END)  
  AS  
  '生物' 
  FROM dbo.[StudentScores] 
  GROUP  
  BY

 

 

接下来我们来看看第二个小列子。有一个游戏玩家充值表（仅仅为了说明，举的一个小例子），

CREATE    TABLE [Inpours]( [ID]  
  INT  
  IDENTITY(1,1), [UserName] NVARCHAR(20), --游戏玩家 [CreateTime] DATETIME, --充值时间 [PayType] NVARCHAR(20), --充值类型 [Money]  
  DECIMAL, --充值金额 [IsSuccess]  
  BIT, --是否成功 1表示成功， 0表示失败  
  CONSTRAINT [PK_Inpours_ID]  
  PRIMARY  
  KEY(ID)) INSERT  
  INTO Inpours  
  SELECT  
  '张三',  
  '2010-05-01',  
  '支付宝', 50, 1 INSERT  
  INTO Inpours  
  SELECT  
  '张三',  
  '2010-06-14',  
  '支付宝', 50, 1 INSERT  
  INTO Inpours  
  SELECT  
  '张三',  
  '2010-06-14',  
  '手机短信', 100, 1 INSERT  
  INTO Inpours  
  SELECT  
  '李四',  
  '2010-06-14',  
  '手机短信', 100, 1 INSERT  
  INTO Inpours  
  SELECT  
  '李四',  
  '2010-07-14',  
  '支付宝', 100, 1 INSERT  
  INTO Inpours  
  SELECT  
  '王五',  
  '2010-07-14',  
  '工商银行卡', 100, 1 INSERT  
  INTO Inpours  
  SELECT  
  '赵六',  
  '2010-07-14',  
  '建设银行卡', 100, 1

 

下面来了一个统计数据的需求，要求按日期、支付方式来统计充值金额信息。这也是一个典型的行转列的例子。我们可以通过下面的脚本来达到目的

代码   
SELECT           CONVERT 
    ( 
    VARCHAR 
    ( 
    10     ), CreateTime,      120     )      AS 
     CreateTime, 
             CASE      PayType  
    WHEN 
      
    ' 
    支付宝 
    ' 
          
    THEN 
      
    SUM 
    ( 
    Money 
    )  
    ELSE 
      
    0           END 
      
    AS 
      
    ' 
    支付宝 
    ' 
    , 
             CASE      PayType  
    WHEN 
      
    ' 
    手机短信 
    ' 
         
    THEN 
      
    SUM 
    ( 
    Money 
    )  
    ELSE 
      
    0           END 
      
    AS 
      
    ' 
    手机短信 
    ' 
    , 
             CASE      PayType  
    WHEN 
      
    ' 
    工商银行卡 
    ' 
       
    THEN 
      
    SUM 
    ( 
    Money 
    )  
    ELSE 
      
    0           END 
      
    AS 
      
    ' 
    工商银行卡 
    ' 
    , 
             CASE      PayType  
    WHEN 
      
    ' 
    建设银行卡 
    ' 
       
    THEN 
      
    SUM 
    ( 
    Money 
    )  
    ELSE 
      
    0           END 
      
    AS 
      
    ' 
    建设银行卡 
    ' 
     
     FROM      Inpours 
     GROUP       
    BY 
     CreateTime, PayType

 

如图所示，我们这样只是得到了这样的输出结果，还需进一步处理，才能得到想要的结果

SELECT CreateTime, ISNULL( SUM([支付宝]) , 0)  AS [支付宝] , ISNULL( SUM([手机短信]) , 0)  AS [手机短信] , ISNULL( SUM([工商银行卡]), 0)  AS [工商银行卡] , ISNULL( SUM([建设银行卡]), 0)  AS [建设银行卡] FROM(  SELECT  CONVERT( VARCHAR(10), CreateTime, 120)  AS CreateTime,  CASE PayType  WHEN  '支付宝'  THEN  SUM(Money)  ELSE 0  END  AS  '支付宝' ,  CASE PayType  WHEN  '手机短信'  THEN  SUM(Money)  ELSE 0  END  AS  '手机短信',  CASE PayType  WHEN  '工商银行卡'  THEN  SUM(Money)  ELSE 0  END  AS  '工商银行卡',  CASE PayType  WHEN  '建设银行卡'  THEN  SUM(Money)  ELSE 0  END  AS  '建设银行卡'  FROM Inpours  GROUP  BY CreateTime, PayType) T GROUP  BY

其实行转列，关键是要理清逻辑，而且对分组（Group by）概念比较清晰。上面两个列子基本上就是行转列的类型了。但是有个问题来了，上面是我为了说明弄的一个简单列子。实际中，可能支付方式特别多，而且逻辑也复杂很多，可能涉及汇率、手续费等等(曾经做个这样一个)，如果支付方式特别多，我们的CASE WHEN 会弄出一大堆，确实比较恼火，而且新增一种支付方式，我们还得修改脚本如果把上面的脚本用动态SQL改写一下，我们就能轻松解决这个问题

代码

 

下面是通过PIVOT来进行行转列的用法，大家可以对比一下，确实要简单、更具可读性（呵呵，习惯的前提下）

代码   
SELECT       
         CreateTime,      [     支付宝     ]      ,      [ 
    手机短信 
    ] 
    , 
              [     工商银行卡     ]      ,      [ 
    建设银行卡 
    ] 
     
     FROM      
 ( 
          SELECT           CONVERT     (     VARCHAR 
    ( 
    10     ), CreateTime,      120     )      AS      CreateTime,PayType,      Money      
          FROM      Inpours 
 ) P 
 PIVOT ( 
                  SUM     (     Money     ) 
                  FOR      PayType      IN      
             (     [     支付宝     ]     ,      [ 
    手机短信 
    ] 
    ,  
    [ 
    工商银行卡 
    ] 
    ,  
    [ 
    建设银行卡 
    ] 
    ) 
       )      AS      T 
     ORDER           BY      CreateTime

 

有时可能会出现这样的错误：

消息 325，级别 15，状态 1，第 9 行

'PIVOT' 附近有语法错误。您可能需要将当前数据库的兼容级别设置为更高的值，以启用此功能。有关存储过程 sp_dbcmptlevel 的信息，请参见帮助。

这个是因为：对升级到 SQL Server 2005 或更高版本的数据库使用 PIVOT 和 UNPIVOT 时，必须将数据库的兼容级别设置为 90 或更高。有关如何设置数据库兼容级别的信息，请参阅 sp_dbcmptlevel (Transact-SQL)。 例如，只需在执行上面脚本前加上 EXEC sp_dbcmptlevel Test, 90; 就OK了， Test 是所在数据库的名称。

 

下面我们来看看列转行，主要是通过UNION ALL ,MAX来实现。假如有下面这么一个表

代码

CREATE           TABLE      ProgrectDetail 
 ( 
     ProgrectName              NVARCHAR     (     20     ),      --     工程名称      
         OverseaSupply             INT     ,               --     海外供应商供给数量      
         NativeSupply              INT     ,               --     国内供应商供给数量      
         SouthSupply               INT     ,               --     南方供应商供给数量      
         NorthSupply               INT                     --     北方供应商供给数量      
     ) 

     INSERT           INTO      ProgrectDetail 
     SELECT           '     A     '     ,  
    100     ,      200     ,      50     ,      50      
     UNION           ALL      
     SELECT           '     B     '     ,  
    200     ,      300     ,      150     ,      150      
     UNION           ALL      
     SELECT           '     C     '     ,  
    159     ,      400     ,      20     ,      320      
     UNION           ALL      
     SELECT           '     D     '     ,  
    250     ,      30     ,      15     ,      15

 

我们可以通过下面的脚本来实现，查询结果如下图所示

代码

SELECT      ProgrectName,      '     OverseaSupply     '           AS      Supplier, 
              MAX     (OverseaSupply)      AS           '     SupplyNum     ' 
     
     FROM      ProgrectDetail 
     GROUP           BY      ProgrectName 
     UNION           ALL      
     SELECT      ProgrectName,      '     NativeSupply     '           AS 
     Supplier, 
              MAX     (NativeSupply)      AS           '     SupplyNum     ' 
     
     FROM      ProgrectDetail 
     GROUP           BY      ProgrectName 
     UNION           ALL      
     SELECT      ProgrectName,      '     SouthSupply     '           AS 
     Supplier, 
              MAX     (SouthSupply)      AS           '     SupplyNum     ' 
     
     FROM      ProgrectDetail 
     GROUP           BY      ProgrectName 
     UNION           ALL      
     SELECT      ProgrectName,      '     NorthSupply     '           AS 
     Supplier, 
              MAX     (NorthSupply)      AS           '     SupplyNum     ' 
     
     FROM      ProgrectDetail 
     GROUP           BY      ProgrectName

 

 

用UNPIVOT 实现如下：

代码

SELECT      ProgrectName,Supplier,SupplyNum 
     FROM       
 ( 
          SELECT      ProgrectName, OverseaSupply, NativeSupply, 
            SouthSupply, NorthSupply 
           FROM      ProgrectDetail 
 )T 
 UNPIVOT  
 ( 
     SupplyNum      FOR      Supplier      IN      
     (OverseaSupply, NativeSupply, SouthSupply, NorthSupply ) 
 ) P