AI 助理
备案控制台
开发者社区 数据库 文章 正文

[转载]SQL行转列，列转行

2012-08-09 1318
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 原文地址：http://www.cnblogs.com/kerrycode/archive/2010/07/28/1786547.html 行转列，列转行是我们在开发过程中经常碰到的问题。行转列一般通过CASE WHEN 语句来实现，也可以通过 SQL SERVER 2005 新增的运算符PIVOT来实现。

原文地址：http://www.cnblogs.com/kerrycode/archive/2010/07/28/1786547.html

 行转列，列转行是我们在开发过程中经常碰到的问题。行转列一般通过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
复制代码

 

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

复制代码
代码
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
复制代码

 

文章标签：
SQL
数据库
关键词：
SQL行转列
SQL列转行
SQL行转列列转行
布雷泽
目录
相关文章
JavaPub
|
6月前
|
SQL
行转列【死磕sql】
行转列【死磕sql】
JavaPub
32 3
阿里云开发者
|
3月前
|
SQL JSON 分布式计算
ODPS SQL ——列转行、行转列这回让我玩明白了！
本文详细介绍了在MaxCompute中如何使用TRANS_ARRAY和LATERAL VIEW EXPLODE函数来实现列转行的功能。
阿里云开发者
579 9
Maynor
|
5月前
|
SQL 大数据 HIVE
每天一道大厂SQL题【Day33】行转列，列转行
每天一道大厂SQL题【Day33】行转列，列转行
Maynor
52 0
每天一道大厂SQL题【Day33】行转列，列转行
魏红斌
|
7月前
|
SQL 关系型数据库 MySQL
SQL脚本列转行
【5月更文挑战第2天】
魏红斌
52 0
魏红斌
|
7月前
|
SQL 关系型数据库 MySQL
SQL脚本行转列
【5月更文挑战第1天】
魏红斌
45 0
sunrr
|
7月前
|
SQL 流计算
Flink SQL提供了行转列的功能，可以通过使用`UNPIVOT`操作来实现
【1月更文挑战第1天】Flink SQL提供了行转列的功能，可以通过使用`UNPIVOT`操作来实现
sunrr
530 0
可为编程
|
SQL 前端开发 关系型数据库
pg库实现sql行转列
这个主题还是比较常见的，行转列主要适用于对数据作聚合统计，如统计某类目的商品在某个时间区间的销售情况。列转行问题同样也很常见。
可为编程
348 0
pg库实现sql行转列
小数志
|
SQL
SQL中的行转列和列转行
SQL是IT行业很多岗位都要求具备的一项能力，对于数据岗位而言更是如此，甚至说扎实的SQL基础也往往是入职这些岗位的必备技能。而在SQL面试中，一道出镜频率很高的题目就是行转列和列转行的问题，可以说这也是一道经典的SQL题目，本文就这一问题做以介绍分享。
小数志
934 0
SQL中的行转列和列转行
赵延东的一亩三分地
|
SQL Oracle 关系型数据库
【SQL开发实战技巧】系列（二十四）：数仓报表场景☞通过执行计划详解”行转列”,”列转行”是如何实现的
本篇文章讲解的主要内容是：***目前Oracle支持的行列互换有两种方式：case when、pivot\unpivot,我将通过几个案例来给大家详解如何通过这两种方式实现“行转列”，“列转行”的需求，并通过执行计划看case when、pivot\unpivot二者的底层逻辑关系以及效率上的影响。***
赵延东的一亩三分地
233 0
【SQL开发实战技巧】系列（二十四）：数仓报表场景☞通过执行计划详解”行转列”,”列转行”是如何实现的
JavaPub
|
SQL
行转列【死磕sql】
行转列【死磕sql】
JavaPub
169 0
行转列【死磕sql】

热门文章

最新文章

  • 1
    PostgreSQL SQL HINT的使用
  • 2
    MS SQL 锁与事务
  • 3
    超有用的sql查询语句。项目、面试都有用
  • 4
    PL/SQL块结构和组成元素
  • 5
    ADO.NET——Command(执行SQL) & DataReader(读取数据库)
  • 6
    （三）mybatisPlus自定义Sql语句
  • 7
    tempdb对SQL Server性能的影响 <转>
  • 8
    SQL Server CLR 使用 C# 自定义存储过程和触发器
  • 9
    【代码分享-SQL】为DB中所有Table增加PK
  • 10
    找到SQL Server数据库历史增长信息
  • 1
    实时计算 Flink版产品使用合集之flink sql ROW_NUMBER()回退更新的机制，有相关文档介绍吗
    96
  • 2
    实时计算 Flink版产品使用合集之ClickHouse-JDBC 写入数据时，发现写入的目标表名称与 PreparedStatement 中 SQL 的表名不一致如何解决
    89
  • 3
    实时计算 Flink版产品使用合集之遇到SQL Server锁表问题如何解决
    52
  • 4
    SQL基础(1)，从三流Linux运维外包到秒杀阿里P7,
    70
  • 5
    实时计算 Flink版产品使用合集之在Mac M1下的Docker环境中开启SQL Server代理的操作步骤是什么
    260
  • 6
    实时计算 Flink版操作报错合集之Flink sql-client 针对kafka的protobuf格式数据建表，报错：java.lang.ClassNotFoundException 如何解决
    233
  • 7
    实时计算 Flink版产品使用合集之在使用Flink SQL向ClickHouse写入数据的过程中出现丢数据或重复数据的情况如何解决
    346
  • 8
    实时计算 Flink版产品使用合集之从MySQL到Flink 1.16.2 Flink-SQL的数据同步工作出现了一个异常如何解决
    138
  • 9
    实时计算 Flink版产品使用合集之将OceanBase的CDC数据导入到Flink SQL的任务的步骤是什么
    66
  • 10
    实时计算 Flink版产品使用合集之是否可以使用 DataStream API 或 Flink SQL 开发任务
    58

    • 相关课程

    更多
  • 如何在 PolarDB-X 中优化慢 SQL
  • SQL完全自学手册
  • SQL Server on Linux入门教程
  • SQL入门与实践
  • 数据库及SQL/MySQL基础
  • SQL进阶及查询

    • 相关电子书

    更多
  • SQL Server 2017
  • GeoMesa on Spark SQL
  • 原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili

    • 相关实验场景

    更多
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 玩转MaxCompute SQL! 30分钟搞定数据分析挖掘
  • 使用SQL语句实现数据表管理
  • 使用SQL语句实现数据查询操作
  • 使用SQL语句管理索引
  • SQLServer数据库的创建
    • 下一篇
    阿里云对象存储OSS收费标准：存储、流量和请求等多个计费项详解