`

老生常谈: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改写一下,我们就能轻松解决这个问题 

代码
DECLARE   @cmdText      VARCHAR ( 8000 );
DECLARE   @tmpSql          VARCHAR ( 8000 );

SET   @cmdText   =   ' SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime, '   +   CHAR ( 10 );
SELECT   @cmdText   =   @cmdText   +   '  CASE PayType WHEN  '''   +  PayType  +   '''  THEN SUM(Money) ELSE 0 END AS  '''   +  PayType 
                
+   ''' , '   +   CHAR ( 10 )   FROM  ( SELECT   DISTINCT  PayType  FROM  Inpours ) T

SET   @cmdText   =   LEFT ( @cmdText LEN ( @cmdText - 2 -- 注意这里,如果没有加CHAR(10) 则用LEFT(@cmdText, LEN(@cmdText) -1)

SET   @cmdText   =   @cmdText   +   '  FROM Inpours     GROUP BY CreateTime, PayType  ' ;

SET   @tmpSql   = ' SELECT CreateTime, '   +   CHAR ( 10 );
SELECT   @tmpSql   =   @tmpSql   +   '  ISNULL(SUM( '   +  PayType   +   ' ), 0) AS  '''   +  PayType   +   ''' , '    +   CHAR ( 10 )
                    
FROM   ( SELECT   DISTINCT  PayType  FROM  Inpours ) T

SET   @tmpSql   =   LEFT ( @tmpSql LEN ( @tmpSql - 2 +   '  FROM ( '   +   CHAR ( 10 );

SET   @cmdText   =   @tmpSql   +   @cmdText   +   ' ) T GROUP BY CreateTime  ' ;
PRINT   @cmdText
EXECUTE  ( @cmdText );

 

下面是通过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

 

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

http://images.cnblogs.com
分享到:
评论

相关推荐

    sql server 注入 sqli_bitbybit

    虽然SQL注入是一种老生常谈的安全漏洞,但本案例展示了一种相对特殊的攻击方法——通过bit by bit的方式来逐步渗透数据库。这种攻击方式需要细致的规划和执行,但一旦成功,可以获得丰富的数据库信息甚至对服务器的...

    vue实现行列转换的一种方法

    行列转换是一个老生常谈的问题,这几天逛知乎有遇到了这个问题。一个前端说,拿到的数据是单列的需要做转换才能够绑定,折腾了好久才搞定,还说这个应该后端直接出数据,不应该让前端折腾。 这个嘛,行列转换在后端...

    老生常谈MongoDB数据库基础操作

    文档(documents)是集合中的基本单元,相当于表格中的行,文档内字段可以自由组合,无需固定模式。 3. **无表结构限制**:与SQL数据库不同,MongoDB不需预定义表结构,这意味着在使用集合前不需要初始化,对于快速...

    老生常谈android中的事件传递和处理机制

    在Android开发中,事件传递和处理机制是相当关键的一个部分,尤其对于用户界面的交互有着决定性的影响。本文主要探讨了Android系统如何处理触摸事件,通过拟人化的比喻来解释事件传递的流程,并通过实际代码示例进行...

    我的WafBypass之路(SQL注入篇)

    去年到现在就一直有人希望我出一篇关于waf绕过的文章,我觉得这种老生常谈的话题也没什么可写的。很多人一遇到waf就发懵,不知如何是好,能搜到的各种姿势也是然并卵。但是积累姿势的过程也是迭代的,那么就有了此文...

    【原创】C#导出数据到EXCEL方法谈(附实例源码和超级无敌详细讲解)

    C#导出数据到EXCEL表格是个老生常谈的问题了,写这篇文章主要是给和我一样的新手朋友提供两种导出EXCEL的方法并探讨一下导出的效率问题,本文中的代码直接就可用,其中部分代码参考其他的代码并做了修改,抛砖引玉,...

    浅谈SQLServer数据库分页

    在SQLServer中使用存储过程实现分页的已经有很多方法了。之前在面试中遇到过这一问题,问如何高效实现数据库分页。刚好上周在业务中也遇到了这个需求,所以在这里简单记录和分享一下。这里以SQLServer的示例数据库...

    老生常谈javascript的类型转换

    转换为字符串是JavaScript中常见的操作,无论是数字、布尔值还是字符串本身,都有`toString()`方法用于将其转换为字符串形式。例如,数字10调用`toString()`方法后会变成字符串"10",布尔值`true`会变成字符串"true...

    电子技术的老生常谈——接地.pdf

    正如标题所言,《电子技术的老生常谈——接地》一文中提到的那样,尽管接地的基本概念在每一次培训和交流中都会被提及,但往往缺乏一个通用而全面的方法论指导。本文旨在深入探讨接地的各种类型、目的以及具体的实施...

    浅谈SQL Server数据库分页

    数据库分页是老生常谈的问题了。如果使用ORM框架,再使用LINQ的话,一个Skip和Take可以搞定。但是有时由于限制,需要使用存储过程来实现。在SQLServer中使用存储过程实现分页的已经有很多方法了。之前在面试中遇到过...

    老生常谈PHP中的数据结构:DS扩展

    在编程中,数据结构的使用对于程序的性能和开发效率具有重要影响。DS扩展通过引入一系列接口和类来丰富PHP的数据结构类型,使得开发者能够更方便地处理数据。 首先,DS扩展定义了几个基本接口,用来规范数据结构的...

    老生常谈ProgressBar、ProgessDialog的用法

    ProgressBar和ProgressDialog是Android开发中常见的两种进度条控件,用于展示任务执行的进度或等待状态。下面我们将深入探讨这两种控件的用法。 首先,ProgressBar是一个可以显示具体进度的组件,它可以是圆形或...

    老生常谈外链 站长要做到对症下药.pps

    老生常谈外链 站长要做到对症下药.pps

    老生常谈javascript的面向对象思想

    在进行JavaScript编程的过程中,面向对象思想一直是一个核心的概念。面向对象编程(OOP)是通过创建对象来模拟现实世界的一种编程范式。在JavaScript中,对象可以通过不同的方法创建和定义。面向对象有三大基本特性...

    老生常谈css中float的用法

    CSS中的`float`属性是一个非常基础且重要的布局工具,它被广泛用于创建多列布局、对齐元素以及处理内容环绕等场景。本文将深入探讨`float`的用法及其在实际设计中的应用。 首先,`float`属性允许元素在当前行内浮动...

    老生常谈js中0到底是 true 还是 false

    在JavaScript中,布尔值(Boolean)`true`和`false`是逻辑运算的基本元素,它们在条件语句、三元运算符以及逻辑运算符中扮演重要角色。然而,JavaScript的数据类型转换规则有时会让初学者感到困惑,特别是当涉及到非...

    老生常谈Listview中onItemClick中的各个参数(推荐)

    本文将详细讲解ListView的`onItemClick`方法中的各个参数及其应用,以及如何在点击事件中获取当前选中行的数据,以便调用相应的服务接口。 `OnItemClickListener`是一个接口,包含一个方法`onItemClick(AdapterView...

Global site tag (gtag.js) - Google Analytics