SQL数据库查询实现行转列与列转行结果SQL语句(适用于SqlServer数据库，oracle需要修改case when语句)

最新推荐文章于 2023-04-19 09:05:06 发布
最新推荐文章于 2023-04-19 09:05:06 发布
阅读量1.4k 收藏
点赞数
分类专栏： 常见实际问题解决
行转列，列转行是我们在开发过程中经常碰到的问题。行转列一般通过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


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


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

 


Code [http://www.oeedu.com] 


     
     

      
      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


 

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

 



Code [http://www.oeedu.com] 


      
      

       
       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 PayTypeWHEN'支付宝'    THENSUM(Money)ELSE0ENDAS '支付宝',
           CASE PayTypeWHEN'手机短信'  THENSUM(Money)ELSE0ENDAS '手机短信',
           CASE PayTypeWHEN'工商银行卡'THENSUM(Money)ELSE0ENDAS '工商银行卡',
           CASE PayTypeWHEN'建设银行卡'THENSUM(Money)ELSE0ENDAS '建设银行卡'
    FROM Inpours
    GROUP BY CreateTime, PayType
) T
GROUP BY CreateTime



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

 

Code [http://www.oeedu.com] 


    
    

     
     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来进行行转列的用法，大家可以对比一下，确实要简单、更具可读性（呵呵，习惯的前提下）


Code [http://www.oeedu.com] 


    
    

     
     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来实现。假如有下面这么一个表

 

代码

buster2014
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
使用SQL语句查询MySQL,SQLServer,Oracle所有数据库名和表名,字段名
09-09
本文例出了使用SQL语句查询MySQL,SQLServer,Oracle所有数据库名和表名的SQL语句，有需要的可以参考下
MS SQL中关于日期时间的转换格式
墨羽离殇的专栏
05-25 993
convert(varchar(10),字段名,转换格式)CONVERT(nvarchar(10),count_time,121) CONVERT为日期转换函数，一般就是在时间类型(datetime,smalldatetime)与字符串类型(nchar,nvarchar,char,varchar) 相互转换的时候才用到的函数的3个参数，第1个参数为，转换后的大小，第2个为转换日期的字段或函数，第
[转载]SQL Server行列转换实现
weixin_34132768的博客
12-17 173
一、Pivot和UnPivot介绍1.Pivot介绍PIVOT用于将列值旋转为列名（即行转列），在SQL Server 2000可以用聚合函数配合CASE语句实现 PIVOT的一般语法是：PIVOT(聚合函数(列) FOR 列 in (…) )AS P 完整语法： table_source PIVOT( 聚合函数（value_column） FOR pivot_column IN(&...
SQL四种方法实现行列转换超详细
热门推荐
楚生辉的博客
09-03 5万+
大家好，我是楚生辉，在未来的日子里我们一起来学习大数据SQL相关的技术，一起努力奋斗，遇见更好的自己！本文详细的介绍了多个方法实现列转行，行转列，并提供了案例的材料，有需要的小伙伴可以自行获取与学习~
SQL中的行列转置
qq_42640067的博客
08-19 1万+
前言 在实际的开发过程中，我们可能会遇到这样的需求：需要将一行数据按照列拆分为多行，或者是需要将多行数据通过扩展列的形式合并为一行。这在SQL上一般称为行列转置，也就是将行转为列，列转为多行。下面我们一起来看看行列转置是如何实现的。 行列转置 1、行转列 行转列实际上是将多行数据按照某个相同的值通过扩展列的形式合并为一行数据。以下通过一个实例来说明。事先准备好3张表：student5表、score5表、subject5表。 student5表数据如下： score5表数据如下： 由于表中数据过多，所以这
sql 查询结果，行转化为列
scimence的专栏
04-19 420
sql 查询结果，行转化为列
C#实现连接SQL Server2012数据库并执行SQL语句的方法
08-29
主要介绍了C#实现连接SQL Server2012数据库并执行SQL语句的方法,结合实例形式较为详细的分析了C#连接SQL Server2012数据库并执行查询、插入等操作的相关实现技巧,需要的朋友可以参考下
通过创建SQLServer 2005到 Oracle10g 的链接服务器实现异构数据库数据转换方案
12-15
1、新建链接服务器  在图1中选中“链接服务器”，右键选择“新建链接服务器”，如图2，配置相关参数。...3、异构数据库访问语句格式 比如我们建立了链接服务器ORADBCONN，而Oracle中在HRSOFT用户下
关系型数据库及SQL语句-SQLSERVER数据库设计与实现210页.doc
04-24
关系型数据库及SQL语句-SQLSERVER数据库设计与实现210页.doc
SQL查询语句行转列横向显示实例解析
01-19
本文分享了两个有关SQL查询语句行转列横向显示的示例，供大家参考，具体内容如下 示例1： 在SQL查询语句行转列横向显示中access中没有CASE，要用IIF代替 select iif(sex= ‘1 ‘, ‘男 ‘, ‘女 ‘) from ...
SQL中如何实现行转列，列转行的操作
12-16
SQL中如何实现行转列，列转行的操作.这里的例子保证让你豁然开朗！
SQLServer行转列实现思路记录
09-10
SQLServer行转列的sql语句有很多，可以使用静态的、动态的、Case When等等，均能实现
Oracle、MySQL和SqlServe三种数据库分页查询语句的区别介绍
01-19
先来定义分页语句将要用到的几个参数： int currentPage ; //当前页 int pageRecord ; //每页显示记录数 以之前的ADDRESSBOOK数据表为例(每页显示10条记录)： 一、SqlServe下载 分页语句 String sql = select ...
SqlServer Mysql数据库修改自增列的值及相应问题的解决方案
12-15
由于之前处理过sql server数据库的迁移工作，尝试过其自增列值的变更，但是通过SQL 语句修改自增列值，是严格不允许的，直接报错（无法更新标识列 ‘自增列名称‘）。sql server我测试是2008、2012和2014，都不允许...
SQL Server数据库实验指导书 实验教程 数据库实验02 通过SQL语句创建与管理数据表.pdf
09-21
6.掌握应用SQL Server Management Studio修改和查看数据库的方法。 7.掌握应用SQL Server Management Studio删除数据库的方法。 8.掌握应用Transact-SQL语句创建数据库的方法。 9.掌握应用Transact-SQL修改和查看...
SQLServer行转列总结
zhanchulan的博客
04-18 1216
SQL 的行转列是一种数据处理技术，可以将查询结果中的多行数据转换为一行数据，方便存储和处理。
SQL中怎么将行转成列？
流楚丶格念的博客
08-23 3万+
我们以MySQL数据库为例，来说明行转列的实现方式。首先，假设我们有一张分数表（tb_score），表中的数据如下图：然后，我们再来看一下转换之后需要得到的结果，如下图：可以看出，这里行转列是将原来的subject字段的多行内容选出来，作为结果集中的不同列，并根据userid进行分组显示对应的score。通常，我们有两种方式来实现这种转换。
sql行转列三个方法
qq_59344662的博客
05-23 1万+
1.行转列sum+if/case when 由多行变一行，group by聚合，由一列变多列，衍生提前。 select uid, sum(if(course='语文',score,NULL)) as `语文`, sum((if(course='数学',score,NULL)) as `数学` from score group by uid; 2.列转行union select uid, '语文' as course，`语文` as score from score where `语文` is
SqlServer行转列&列转行
m0_49844045的博客
01-06 2万+
2、SQLServer 2.1、行转列 -- 第一种方法 select name, max(case cource when '语文' then score else 0 end) as chinese, max(case cource when '数学' then score else 0 end) as math, max(case cource when '英语' then score else 0 end) as english, max(case cource when '物理' then sc
请写出适用于Oracle 数据库的SQL语句，在select获取某列值时，查询结果保留两位小数。
最新发布
05-09
在Oracle数据库中，可以使用ROUND函数来将查询结果保留指定的小数位数。以下是一个示例SQL语句： ``` SELECT ROUND(column_name, 2) FROM table_name; ``` 其中，column_name为需要查询的列名，table_name为表名。该语句将返回查询结果，并将结果保留两位小数。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值