博客
关于我
强烈建议你试试无所不能的chatGPT，快点击我
SQL数据库查询实现行转列与列转行结果SQL语句(适用于SqlServer数据库，oracle需要修改case when语句)
阅读量：2353 次
发布时间：2019-05-10

本文共 12051 字，大约阅读时间需要 40 分钟。

文章来源：
行转列，列转行是我们在开发过程中经常碰到的问题。行转列一般通过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) PPIVOT (                 
     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来实现。假如有下面这么一个表

 

代码

转载地址：http://uygvb.baihongyu.com/

你可能感兴趣的文章
C语言基础---数组、指针之间的相同与不同
查看>>
类的继承的应用场景
查看>>
python3 + selenium------Chrome和Firefox 驱动的使用和版本对应
查看>>
pycharm不同测试框架的设置、unittest测试案例
查看>>
python unittest TestCase间共享数据（全局变量的使用）
查看>>
Python中普通字符串 & json字符串&json对象的区别
查看>>
python中json.dumps()和json.dump() 以及 json.loads()和json.load()的区分
查看>>
Python3中打开文件的方式（With open）
查看>>
python中unittest加载测试用例的4种方法
查看>>
iOS中使用RNCryptor对资源文件加密
查看>>
Device Tree编译工具dtc
查看>>
softlockup/hardlockup原理详细介绍
查看>>
项目管理学习笔记之八风险管理过程总结
查看>>
项目管理学习笔记之九采购管理过程总结
查看>>
solaris常用命令总结
查看>>
邮件安全证书（S/MIME），如何申请邮件证书
查看>>
Go语言基础入门--简介
查看>>
Go语言基础入门--变量,类型
查看>>
Go语言基础入门--数组,切片,map
查看>>
Go语言基础入门--if,for,range,switch
查看>>
喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！-- 愿君每日到此一游！
当前时间: 2024-07-03 05:03:53   当前IP: 18.216.134.4   联系邮箱:javaeecc@qq.com     Copyright © 2020 - 2022 baihongyu.com 京ICP备2021015314号-2
强烈建议你试试无所不能的CHAT-GPT，快点击我
强烈建议你试试无所不能的CHAT-GPT，快点击我