关于 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 UserName

查询结果如图所示，这样我们就能很清楚的了解每位学生所有的成绩了  

 

 

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

 

  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 CreateTime
  

 

其实行转列，关键是要理清逻辑，而且对分组（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