Oracle——行转列与列转行

这篇具有很好参考价值的文章主要介绍了Oracle——行转列与列转行。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

行转列

把某一个字段的值作为唯一值,然后另外一个字段的行值转换成它的列值

转换过程大致如下所示:
列转行oracle,Oracle,mysql,oracle,数据库,mysql
通常情况下,采取group by 唯一字段进行分组,然后依据不同的列进行判断输出就能转换。

创建表和增加测试数据

建表语句和增加测试数据sql如下所示:

create table XJ_TEST_LtoH(
      stuid varchar(20),
      stuname varchar(40),
      coursename varchar(40),
      score int
);


insert into XJ_TEST_LtoH(stuid,Stuname,coursename,score)
values('sc202201001','张三','数学',40);
insert into XJ_TEST_LtoH(stuid,Stuname,coursename,score)
values('sc202201001','张三','语文',50);
insert into XJ_TEST_LtoH(stuid,Stuname,coursename,score)
values('sc202201001','张三','理综',120);
insert into XJ_TEST_LtoH(stuid,Stuname,coursename,score)
values('sc202201011','李四','数学',32);
insert into XJ_TEST_LtoH(stuid,Stuname,coursename,score)
values('sc202201011','李四','语文',45);
insert into XJ_TEST_LtoH(stuid,Stuname,coursename,score)
values('sc202201011','李四','理综',123);
insert into XJ_TEST_LtoH(stuid,Stuname,coursename,score)
values('sc202201031','王五','数学',54);
insert into XJ_TEST_LtoH(stuid,Stuname,coursename,score)
values('sc202201031','王五','语文',56);
insert into XJ_TEST_LtoH(stuid,Stuname,coursename,score)
values('sc202201031','王五','理综',100);

执行完毕后,数据库中当前的数据结构如下:
列转行oracle,Oracle,mysql,oracle,数据库,mysql

同所属的类,对应不同的值,采取不同列进行存储。

方式一:先分组,再统计平铺

将数据根据学员名分组,将学科成绩平铺展示。sql如下所示:

-- 方式一:先分组,再单元拆分
select 
t.stuid,
t.stuname ,
sum(decode(t.coursename,'数学',t.score,0)) "数学",
sum(decode(t.coursename,'语文',t.score,0)) "语文",
sum(decode(t.coursename,'理综',t.score,0)) "理综"
from XJ_TEST_LtoH t group by t.stuname,t.stuid;

列转行oracle,Oracle,mysql,oracle,数据库,mysql

方式二:使用Oracle11g自带函数PIVOT实现

Oracle11g及以后自带函数PIVOT就能实现上述的效果,且代码量很小。

select * fromPIVOT (
	sum(xxx /**行转列需要显示数据的列**/) 
	for 
	XXX   /**需要行转列的列**/ 
	in(mm,nn) /**转换后列的值**/
)

具体的使用方式,如下:

-- 方式二:Oracle11g之后提供了自带函数PIVOT
select * from XJ_TEST_LtoH 
pivot (
      sum(score /**行转列需要显示数据的列**/) 
      for
      coursename /**需要行转列的列**/ 
      in('语文' as 语文,'数学' as 数学,'理综' as 理综) /**转换后列的值**/
);

列转行oracle,Oracle,mysql,oracle,数据库,mysql

列转行

把一行当中的列的字段按照行的唯一值转换成多行数据。

还是上面的栗子,先创建测试表和增加测试数据。

创建表和增加测试数据

------ 列转行前的表创建
create table XJ_TEST_HL as (
select 
t.stuid,
t.stuname ,
sum(decode(t.coursename,'数学',t.score,0)) "数学",
sum(decode(t.coursename,'语文',t.score,0)) "语文",
sum(decode(t.coursename,'理综',t.score,0)) "理综"
from XJ_TEST_LtoH t group by t.stuname,t.stuid);

这种语法,就能直接将查询到的数据信息,以及表结构中字段类型等信息,映射成一张新的表。

此时的数据结构如下所示:
列转行oracle,Oracle,mysql,oracle,数据库,mysql

方式一:union all 单个合并

查询每个需要拆分的列数据信息,以相同的列名接收,再将数据合并。

-- 方式一:先查询单个,再将所有数据拼接
select * from (
select t.stuid,t.stuname,'语文' as coursename,t.语文 as score from XJ_TEST_HL t
union all
select t.stuid,t.stuname,'数学' as coursename,t.数学 as score from XJ_TEST_HL t
union all
select t.stuid,t.stuname,'理综' as coursename,t.理综 as score from XJ_TEST_HL t
) p order by p.stuname;

列转行oracle,Oracle,mysql,oracle,数据库,mysql

方式二:unpivot 函数实现

语法如下所示:

select 字段 from 数据集

unpivot(自定义列名/*列的值*/ for 自定义列名 in(列名))

【注意】这里的是 unpivot ,不是 pivot !

-- 方式二
-- 语法  select 字段 from 数据集 unpivot(自定义列名/*列的值*/ for 自定义列名 in(列名))
select * from XJ_TEST_HL
unpivot (score for coursename in(语文,数学,理综));

列转行oracle,Oracle,mysql,oracle,数据库,mysql

总结

pivotunpivot不太好理解,并且属于oracle特有,针对别的类型数据库,可能方式一更好点。

资料参考

oracle怎么实现行列转换文章来源地址https://www.toymoban.com/news/detail-554838.html

到了这里,关于Oracle——行转列与列转行的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 搞定mysql的 行转列(7种方法) 和 列转行

    一、行转列 1、使用case…when…then  2、使用SUM(IF()) 生成列 3、使用SUM(IF()) 生成列 + WITH ROLLUP 生成汇总行 4、使用SUM(IF()) 生成列,直接生成汇总结果,不再利用子查询 5、使用SUM(IF()) 生成列 + UNION 生成汇总行,并利用 IFNULL将汇总行标题显示为 Total 6、动态查询列值不确定的情况

    2024年03月12日
    浏览(52)
  • MySQL中的面试题——行转列(列转行)手把手详解思想

    目录 1、什么是行转列,列转行 ? 1.1、概念: 1.2、行转列和列转行的思想 1.2.1、行转列的思想? 1.2.2、列转行的思想? 3、实操讲解 3.1、实现行转列  3.2、总结:行转列实现的两种方法    3.3、实现列转行  3.4、总结 :列转行 4、动手练习         在MySQL中,行转列(

    2024年02月03日
    浏览(58)
  • SQL行转列、列转行(SQL Server版)

    在SQL Server中使用SQL实现行转列、列转行,可以使用多种方法,在SQL 2005以前可以使用case when then...语句,但这种方法的问题在于列举的列名要写死,如果列名很多,case when 语句会很长,并不优雅。所以需要SQL Server提供新的语句能够实现。在2005版本就推出了pivot/unpivot,

    2024年02月08日
    浏览(57)
  • mysql 行转列

    以下是其中比较常见的几种方法: 使用GROUP_CONCAT函数 可以使用GROUP_CONCAT函数将多行数据合并为一行,并以逗号或其他分隔符进行分隔。通过SELECT语句和GROUP BY子句,可以将数据行转换为列。具体语法如下: 其中,id表示非透视列,status表示需要转换为列的字段,value表示需要

    2024年02月16日
    浏览(52)
  • Mysql行转列函数

    group_concat() 例: select oid from Test;  select group_concat( oid ) from Test;

    2024年02月14日
    浏览(39)
  • 5分钟搞懂MySQL - 行转列

    小伙伴想精准查找自己想看的MySQL文章?喏 → MySQL专栏目录 | 点击这里   MySQL 行转列 ,对经常处理数据的同学们来说,一定是不陌生的,甚至是印象深刻,因为它大概率困扰过你,让你为之一愣~ 但当你看到本文后,这个问题就不在是问题,及时收藏,以后谁再问你这个问

    2023年04月08日
    浏览(55)
  • Mysql 行转列,把逗号分隔的字段拆分成多行

    源数据 变更后的数据 第一种 先执行下面的SQL,看不看能不能执行,如果有结果,代表数据库版本是可以的,可以看下面和自己表关联的SQL,如果不行用第二种。 示例SQL 和业务结合在一起使用 其核心在于 mysql.help_topic ,但是版本太低的数据库版本不支持,如果不支持,可以

    2024年02月02日
    浏览(41)
  • Mysql一维表转二维表,动态的行转列

    背景 想利用Grafana做数据展示,将一维的长表优化成二维数据表格展示。 将一维表转换为二维表,也就是将行转换为列,可以使用MySQL的PIVOT语句来完成。 PIVOT是一种在关系型数据库中将行转换为列的技术。在MySQL中,可以使用PIVOT语句将普通的查询结果转换为一个带有动态列

    2024年02月15日
    浏览(40)
  • mysql~GROUP_CONCAT实现关系表的行转列

    GROUP_CONCAT 是 MySQL 中用于将查询结果集中的多行数据合并为单个字符串的聚合函数。它将每行数据的指定字段值连接起来,并以指定的分隔符分隔,最终返回一个包含所有值的字符串。 以下是 GROUP_CONCAT 函数的一般语法: column_name:要连接的字段名。 SEPARATOR:可选参数,用于

    2024年04月12日
    浏览(34)
  • DBeaver连接mysql、oracle数据库

    1) 下载DBeaver https://dbeaver.io/download/,并安装 2) 新建数据库连接    3)选择mysql驱动程序  4)填写连接设置内容 5)点击 “编辑驱动设置”,并填写相关信息  6)选择本地自己下载的驱动库jar包 (也可以点击下载驱动)  7)完成后,点击测试连接,可以查看是否连接成功

    2024年02月05日
    浏览(65)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包