Oracle中的行列转换

article/2025/9/27 21:20:48

目录

一、行转列（一）

二、行转列（二） 

三、列转行（一）

四、列转行（二）

行列转换是指将行数据转换为列数据，或将列数据转换为行数据的过程。这通常使用的办法是用PIVOT和UNPIVOT函数来实现。这里描述两种方法分别实现行列转换！！！

首先创建表：

学生表：student;--包括学生号，姓名，年纪，性别，生日

教师表：teacher;--包括教师编号，姓名

课程表：course；--包括课程编号，课程名称，对应教师

学生成绩表：sc；--包括学生号，课程编号，成绩

创建表的脚本如下：

--学生 student表
drop table student;
create table student(
sno varchar2(10) primary key,
sname varchar2(20),
sage number(2),
ssex varchar2(5),
birthday date
);
--教师 teacher表
drop table teacher;
create table teacher(
tno varchar2(10) primary key,
tname varchar2(20)
);
--课程 course表
drop table course;
create table course(
cno varchar2(10),
cname varchar2(20),
tno varchar2(20),
constraint pk_course primary key (cno,tno)
);
--学生成绩 sc表
drop table sc;
create table sc(
sno varchar2(10),
cno varchar2(10),
score number(4,2),
constraint pk_sc primary key (sno,cno)
);
/*******初始化学生表的数据******/
insert into student values ('s001','张亍卬',FLOOR(months_between(SYSDATE,date '2000-3-5')/12),'男',date '2000-3-5');
insert into student values ('s002','李殳戋',FLOOR(months_between(SYSDATE,date '2001-2-3')/12),'男',date '2001-2-3');
insert into student values ('s003','吴仝玓',FLOOR(months_between(SYSDATE,date '2002-5-8')/12),'男',date '2002-5-8');
insert into student values ('s004','琴甪',FLOOR(months_between(SYSDATE,date '2000-6-15')/12),'女',date '2000-6-15');
insert into student values ('s005','王讱纩',FLOOR(months_between(SYSDATE,date '2000-8-12')/12),'女',date '2000-8-12');
insert into student values ('s006','李孖伣',FLOOR(months_between(SYSDATE,date '2001-9-20')/12),'男',date '2001-9-20');
insert into student values ('s007','刘辿吒',FLOOR(months_between(SYSDATE,date '2002-10-5')/12),'男',date '2002-10-5');
insert into student values ('s008','萧竦俐',FLOOR(months_between(SYSDATE,date '2003-6-1')/12),'女',date '2003-6-1');
insert into student values ('s009','陈闫邠邡',FLOOR(months_between(SYSDATE,date '2001-1-15')/12),'女',date '2001-1-15');
insert into student values ('s010','陈芃伋',FLOOR(months_between(SYSDATE,date '2001-1-9')/12),'女',date '2001-1-9');
commit;
/******************初始化教师表***********************/
insert into teacher values ('t001', '龚阳');
insert into teacher values ('t002', '谌燕');
insert into teacher values ('t003', '武明星');
commit;
/***************初始化课程表****************************/
insert into course values ('c001','J2SE','t002');
insert into course values ('c002','Java Web','t001');
insert into course values ('c003','SSH','t001');
insert into course values ('c004','Oracle','t001');
insert into course values ('c005','SQL SERVER 2005','t003');
insert into course values ('c006','C#','t003');
insert into course values ('c007','JavaScript','t003');
insert into course values ('c008','DIV+CSS','t001');
insert into course values ('c009','PHP','t003');
insert into course values ('c010','EJB3.0','t002');
commit;
/***************初始化成绩表***********************/
insert into sc values ('s001','c001',78);
insert into sc values ('s002','c001',80);
insert into sc values ('s003','c001',81);
insert into sc values ('s004','c001',60);
insert into sc values ('s001','c002',82);
insert into sc values ('s002','c002',72);
insert into sc values ('s003','c002',81);
insert into sc values ('s001','c007',88);
insert into sc values ('s001','c010',73);
insert into sc values ('s002','c003',69);
insert into sc values ('s002','c008',92);
insert into sc values ('s002','c009',81);
insert into sc values ('s002','c007',85);
insert into sc values ('s002','c010',75);
insert into sc values ('s005','c001',63);
insert into sc values ('s005','c002',96);
insert into sc values ('s005','c007',75);
insert into sc values ('s005','c010',72);
insert into sc values ('s006','c001',72);
insert into sc values ('s007','c001',61);
insert into sc values ('s008','c001',92);
insert into sc values ('s009','c001',58);
insert into sc values ('s010','c001',85);
insert into sc values ('s002','c004',80);
insert into sc values ('s002','c005',70);
insert into sc values ('s002','c006',60);
commit;

一、行转列（一）

使用case when/decode+聚合函数+group by的方法实现行转列；

把sc表进行行转列查询出每个学生每门课程的成绩：

原sc表:

SELECT * FROM sc;--学生成绩表

执行结果展示其中一部分：

 此时要对cno课程编号进行行转列：

select sno,sum(case cno when 'c001' then score end) c001,sum(case cno when 'c002' then score end) c002,sum(case cno when 'c003' then score end) c003,sum(case cno when 'c004' then score end) c004,sum(case cno when 'c005' then score end) c005,sum(case cno when 'c006' then score end) c006,sum(case cno when 'c007' then score end) c007,sum(case cno when 'c008' then score end) c008,sum(case cno when 'c009' then score end) c009,sum(case cno when 'c0010' then score end) c0010
from sc
group by sno  
order by sno;

执行结果：

展示的为每个学生他的每一门课程成绩；

总结：

要求把查询的哪一列转成列名就放在case后面,并把它的列中值进行分类放在when后面；

比如学生成绩表总共就三列（学生号，课程编号，学生成绩），我们要查询每个学生的每科成绩展示，就需要对课程编号cno进行分类转换，因此把课程编号cno放在case后面，然后把课程编号cno中所包含的所有值进行分类，即全部课程科目c001--c0010，分类放在when的后面！！

要把哪一列内容放在列中值中就放在then 后面；

意思就是我们最后要看的结果值，比如对应上面查询，要查看的是学生成绩score，此时就把学生成绩score放在then的后面即可。

这种办法可以实现我们对需求的解决实现，但是使用比较麻烦，可能会理解错误，而且代码语句写的比较多，因此可以换种方法来更简单实现行转列！！！

二、行转列（二） 

使用PIVOT函数，可以将行数据转换为列数据，并且可以在同一查询中汇总和筛选数据。

基本语法格式如下：

PIVOT(被聚合的列 FOR 行转列的列 in(列中值1,列中值2...))select *
from  表
pivot (聚合函数(被聚合的列) for 行转列的列 in (列中值1,,列中值2植..))

批注：

被聚合的列：变成列中值的列；

行转列的列：由列中值变为列名的列 ； 

列中值1,列中值2..：新增加的列名（即为行转列的列中的列中值）就是列中值1，列中值2...。

备注：被聚合的列要加聚合函数。

或者另一种理解：

SELECT *
FROM (SELECT column1, column2, column3 FROM table_name)
PIVOT (aggregate_function(column2) FOR column1 IN ('value1' AS alias1, 'value2' AS alias2, ...));

其中，PIVOT中的column1是要转换为列的列，column2是要汇总的列，alias是列的别名。

那么此时“把sc表行转列查询每个学生每门课程的成绩”可写为：

select *
from sc
pivot(sum(score) for cno in('c001' c001,'c002' c002,'c003' c003,'c004' c004,'c005' c005,'c006' c006,'c007' c007,'c008' c008,'c009' c009,'c010' c010))
order by sno;

其中，as可加可不加，对于列中值一定要加单引号。同时运行结果是和之前的一致。如图所示：

使用PIVOT函数时，需要注意以下几点：

  • PIVOT函数必须在FROM子句中使用，因此需要将原始查询包装在一个子查询中。
  • aggregate_function是要应用于column2的聚合函数，可以是SUM、AVG、COUNT、MAX、MIN等。
  • FOR子句指定要在新列中显示的值。在IN子句中指定这些值，并在别名中指定新列的名称。

三、列转行（一）

使用union all方法实现列转行；

比如：有一张员工表emp，请用一条sql显示如下格式
  ENPNO  KEY     VALUE 
  7369  ENAME    SMITH
  7369  JOB      CLERK
  7369  MGR      7902

先看原员工表格式：

select * from emp;

 通过对比发现是将原表中的列和其对应值转换为行式展现，同时为其定义了新的列名分别为ENPNO 、KEY 、VALUE 。那么用union all的方式实现的语句为：

select * from (select empno,'ENAME' KEY,ENAME VALUE FROM EMP UNION ALLselect empno,'JOB' KEY,TO_CHAR(JOB) VALUE FROM EMPUNION ALLselect empno,'MGR' KEY,TO_CHAR(MGR) VALUE FROM EMPUNION ALLselect empno,'HIREDATE' KEY,TO_CHAR(HIREDATE) VALUE FROM EMPUNION ALLselect empno,'SAL' KEY,TO_CHAR(SAL) VALUE FROM EMPUNION ALLselect empno,'COMM' KEY,TO_CHAR(COMM) VALUE FROM EMPUNION ALLselect empno,'DEPTNO' KEY,TO_CHAR(DEPTNO) VALUE FROM EMP)
WHERE EMPNO=7369;

简单理解为：查询该员工编号对应的每一条列信息，对列中值进行格式转换统一，然后使用union all进行并集为一个数据集合作为参考表，最后加判断条件完成列转换。那么看下这种方式的运行结果：

通过改图发现确实已经完成了目的需求的格式转换。不过此方法同样比较繁琐，代码量也比较多，所以可以换另外一种方法实现同样的效果。

四、列转行（二）

UNPIVOT函数可以将列数据转换为行数据。基本语法如下：

unpivot 列转行自动去空 如果要留住空值 在unpivot 后加上 include nullsunpivot(被聚合的列的新列名 for  列转行的列的新列名 in (字段1,字段2...))

被聚合的列的新列名：指的是目标结果集的列名，按照目标结果集来填写，即原来聚合的数据如这里的nums，列转行之前的列中值放在取了新名字的这个列中；
列转行的列的新列名：指的是要列转行的列名的集合新名字，既创建一个新的列来存储要列转行的列，如这里的name，他的列中值在列传行之前为原视图的多个列；
字段1,字段2...：指的是要列转行的列名，既为要放到列转行的列的新列名里的列中值，就是列转行之前视图的多个列。

完整格式：

SELECT *
FROM table_name
UNPIVOT (column3 FOR column1 IN (column2, column3, ...));

column1是要转换为行的列，column2和column3是要转换的列。

那么此时使用UNPIVOT函数完成上个问题的列转行方法就可以写为：

select *
from (SELECT empno,ENAME,JOB,TO_CHAR(MGR) MGR,TO_CHAR(HIREDATE) HIREDATE,TO_CHAR(SAL) SAL,TO_CHAR(COMM) COMM,TO_CHAR(DEPTNO) DEPTNOFROM EMPWHERE EMPNO=7369)
unpivot include nulls(VALUE for KEY IN(ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO));

这里在列转行时对表中每一列做了格式统一，最后运行结果和第一种方法一样。如图所示：

使用UNPIVOT函数时，需要注意以下几点：

  • UNPIVOT函数必须在FROM子句中使用，因此需要将原始查询包装在一个子查询中。
  • FOR子句指定要转换为行的列。
  • IN子句指定要转换的列。

http://chatgpt.dhexx.cn/article/ocLZ9xR9.shtml

相关文章

SQL Server行列转换

SQL Server行列转换

1、行列转换 创建创建学生成绩表并添加学生成绩信息。 /*-创建学生成绩表-*/ CREATE TABLE StuScore (StuName VARCHAR(20), --姓名Subject VARCHAR(20), --科目Score INT --成绩 );/*-添加学生成绩信息-*/ INSERT INTO StuScore VALUES(张三,语文,60); I…
阅读更多...
SQL 行列转换

SQL 行列转换

方法1 select name, sum(case when course物理 then score else 0 end) as 物理, sum(case when course英语 then score else 0 end) as 英语 from T1方法2 动态SQL命令的执行效率往往不高，因为动态拼接的原因，导致数据库（查询优化器&…
阅读更多...
SQL：行列转换

SQL：行列转换

参考自：https://www.cnblogs.com/janneystory/p/5622142.html 案例： 表scores 请转成的横表是这样子的： 答案; select 姓名, SUM(case 课程 when 语文 then 分数 else 0 end) as 语文, SUM(case 课程 when 数学 then 分数 else 0 end) a…
阅读更多...
【SQL】MySQL 数据库的行列转换

【SQL】MySQL 数据库的行列转换

报表系统中经常需要行列转换，在 SQL Server 等数据库中可以用 PIVOT 、UNPIVOT 来实现，但是在 MySQL 数据库中却不支持，下面介绍 MySQL 中的行列转换的实现方法。 1.行转列 例如下面是数据库中的原始表格： namesubjectscore张三…
阅读更多...
SQL四种方法实现行列转换超详细

SQL四种方法实现行列转换超详细

前言 大家好，我是楚生辉，在未来的日子里我们一起来学习大数据SQL相关的技术，一起努力奋斗，遇见更好的自己！ 本文详细的介绍了多个方法实现列转行，行转列，并提供了案例的材料，有需要的…
阅读更多...
什么是xml解析？xml解析的有几种常用的解析方式？

什么是xml解析？xml解析的有几种常用的解析方式？

xml解析概述 XML解析主要为两种：DOM和SAX解析 DOM:文档对象模型，这种方式是W3C推荐的处理XML的一种方式 SAX解析不是官方标准，属于开源社区XML-DEV,几乎所有的XML解析器都支持它 XML解析开发包： JAXP:是sun公司推出的解析标准实…
阅读更多...
JAVA解析xml的五种方式比较

JAVA解析xml的五种方式比较

1)DOM解析 DOM是html和xml的应用程序接口(API)，以层次结构（类似于树型）来组织节点和信息片段，映射XML文档的结构，允许获取和操作文档的任意部分，是W3C的官方标准【优点】①允许应用程序对数据和结构做出更改…
阅读更多...
XML解析的几种方式

XML解析的几种方式

xml的四种解析方式实例 一、DOM（Document Object Model）解析方式 在应用程序中,基于DOM的xml分析器将xml文档解析成一个对象模型的集合(通常称DOM树),应用程序正是通过对这个对象模型的操作,来实现对xml数据的操作.通过DOM接口应用程序可以在任何时候访问…
阅读更多...
C++解析XML文件

C++解析XML文件

新的一周又来啦，这周我要分享的是使用C库解析XML文件和JSON文件，在本篇博客中我主要讲解析XML文件的相关知识，在下篇博客讲述有关解析JSON文件的相关。在解析XML文件时我使用的解析库是tinyXML2,编译平台是VS2019。希望看完本篇博客能对你有所…
阅读更多...
解析XML的几种方式

解析XML的几种方式

XML现在已经成为一种通用的数据交换格式,它的平台无关性,语言无关性,系统无关性,给数据集成与交互带来了极大的方便。对于XML本身的语法知识与技术细节,需要阅读相关的技术文献,这里面包括的内容有DOM(Document Object Model),DTD(Document Type Definition),SAX(Simple API fo…
阅读更多...
C# 读取XML文件的几种方式

C# 读取XML文件的几种方式

在开发过程中，我们有时会需要保存到本地一些结构化数据或者配置信息，这时就可以选择用xml文件。当然xml的用途也不仅仅是这些。 这一篇来谈一谈关于读取xml文件的几种方式； 我们有以下两个文件，一个是带有id属性的，一个…
阅读更多...
Android解析XML的三种方式

Android解析XML的三种方式

在Android中提供了三种解析XML的方式:DOM(Document Objrect Model),SAX(Simple API XML),以及Android推荐的Pull解析方式. 如图： 本篇博客使用的xml文件如下：（这里是获取网络XML）person.xml <?xml version="1.0" encoding="UTF-8"?> <pers…
阅读更多...
XML解析之SAX方式

XML解析之SAX方式

XML文件解析方式之一是SAX方式&#xff0c;SAX解析方式会逐行地去扫描XML文档&#xff0c;当遇到标签时会触发解析处理器&#xff0c;采用事件处理的方式解析XML (Simple API for XML) 。SAX是一个用于处理XML事件驱动的“推”模型&#xff0c;虽然不是官方标准&#xff0c;但它…
阅读更多...
实现XML解析的几种技术

实现XML解析的几种技术

XML在各种开发中都广泛应用&#xff0c;Android也不例外。作为承载数据的一个重要角色&#xff0c;如何读写XML成为Android开发中一项重要的技能。今天就由我向大家介绍一下在Android平台下几种常见的XML解析和创建的方法。 在Android中&#xff0c;常见的XML解析器分别为SAX解…
阅读更多...
MATLAB中的均值与方差求法（mean,var,std函数使用）

MATLAB中的均值与方差求法（mean,var,std函数使用）

目录 均值-mean()函数的用法方差var函数std函数 均值-mean()函数的用法 对于矩阵A&#xff1a; &#xff08;1&#xff09;mean(A,1)为对矩阵A的列求均值 &#xff08;2&#xff09;mean(A,2)为对矩阵A的行求均值 &#xff08;3&#xff09;mean(A)当传入的参数只有矩阵时&a…
阅读更多...
matlab怎么方差函数,密度函数已知，怎么用matlab求其数学期望和方差？

matlab怎么方差函数,密度函数已知，怎么用matlab求其数学期望和方差？

在没有先验知识的情况下是不可能的&#xff0c;在你已知它可能是哪种分布(或者哪些种分布之一)的情况下可以求出分布的参数www.mh456.com防采集。 因为你的是密度函2113数&#xff0c;所以不会5261是离散型随机变量&#xff0c;如果你有4102概率密度函数的表达式的话&#xff0…
阅读更多...
matlab怎么算方差和标准差,matlab怎么求矩阵的均值和标准差 看完你就知道了

matlab怎么算方差和标准差,matlab怎么求矩阵的均值和标准差 看完你就知道了

有时候我们在使用matlab编程计算的时候&#xff0c;想求矩阵的均值和方差&#xff0c;怎么求呢&#xff0c;下面来分享一下方法 工具/材料 matlab 求矩阵的均值和标准差方法 matlab求矩阵均值方法 01 第一步打开matlab命令行窗口&#xff0c;新建一个a[1 2 3;4 5 6;7 8 9]矩阵&…
阅读更多...
剩余方差matlab,用matlab的var函数求方差

剩余方差matlab,用matlab的var函数求方差

什么是方差和标准差&#xff1f; 方差(variance) 和标准差(standard variance) 方差&#xff1a;表示一组离散数据偏离平均值的程度。 公式&#xff1a; 方差公式 方差的算术平方根称为该随机变量的标准差。 在Matlab中&#xff0c;方差用var函数求&#xff0c;标准差用std函数…
阅读更多...
matlab里方差分析的盒子图怎么看,Matlab方差分析

matlab里方差分析的盒子图怎么看,Matlab方差分析

Matlab 方差分析(T检验) 在工农业生产和科学研究中,经常遇到这样的问题:影响产品产量、质量的因素很多,我们需要了解在这众多的因素中,哪些因素对影响产品产量、质量有显著影响.为此,要先做试验,然后对测试的结果进行分析.方差分析就是分析测试结果的一种方法.在方差分析中,把在…
阅读更多...
20171205_Matlab求方差，均值，均方差，协方差的函数

20171205_Matlab求方差，均值，均方差，协方差的函数

1、 均值 数学定义&#xff1a; Matlab函数&#xff1a;mean >>X[1,2,3] >>mean(X)2 如果X是一个矩阵&#xff0c;则其均值是一个向量组。mean(X,1)为列向量的均值&#xff0c;mean(X,2)为行向量的均值。 >>X[12 3 45 6] >>mean(X,1)[2.5,3.5, 4.5] &g…
阅读更多...
推荐文章

Copyright @ 2022~2023