Oracle行转列函数,列转行函数

Oracle行转列函数,列转行函数

Oracle 可以通过PIVOT,UNPIVOT,分解一行里面的值为多个列,及来合并多个列为一行。

PIVOT

PIVOT是用于将行数据转换为列数据的查询操作(类似数据透视表)。通过使用PIVOT,您可以按照特定的列值将数据进行汇总,并将其转换为新的列。

语法

pivot(聚合函数 for 需要转为列的字段名 in(需要转为列的字段值))

SELECT *
FROM (-- 源数据查询SELECT column1, column2, ..., pivot_column, value_columnFROM your_source_table
)
PIVOT (-- 聚合函数和列定义aggregate_function(value_column)FOR pivot_column IN (value1 AS alias1, value2 AS alias2, ..., valuen AS aliasn)
);
  • aggregate_function:指定用于对value_column进行聚合操作的函数,如SUMAVG等。(FOR关键字前面的部分只能使用聚合函数)

  • value_column: 指定要聚合的源数据列。

  • pivot_column: 指定要透视的列,其唯一值将被用作新列的列头。且源数据查询的select中必须包含这个字段,以便PIVOT函数可以使用到它。(可以理解为用这个字段来进行group by

  • value1 AS alias1, value2 AS alias2, ..., valuen AS aliasn: 为透视列的每个唯一值指定一个别名,这些别名将成为新列的列头。遗憾的是这里不是使用子查询

准备

CREATE TABLE sales_data (product_name VARCHAR2(100),region VARCHAR2(50),sale_month VARCHAR2(10),sale_amount NUMBER
);
-- 商品 A 在不同地区的销售数据
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product A', 'North', '2024-01', 5000);
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product A', 'South', '2024-01', 7000);
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product A', 'West', '2024-01', 4500);INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product A', 'North', '2024-02', 8000);
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product A', 'South', '2024-02', 7500);
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product A', 'West', '2024-02', 6000);INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product A', 'North', '2024-03', 7000);
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product A', 'South', '2024-03', 8500);
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product A', 'West', '2024-03', 6200);-- 商品 B 在不同地区的销售数据
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product B', 'North', '2024-01', 6000);
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product B', 'South', '2024-01', 8000);
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product B', 'West', '2024-01', 5500);INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product B', 'North', '2024-02', 7000);
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product B', 'South', '2024-02', 9000);
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product B', 'West', '2024-02', 6500);INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product B', 'North', '2024-03', 7800);
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product B', 'South', '2024-03', 9200);
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product B', 'West', '2024-03', 6900);-- 商品 C 在不同地区的销售数据
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product C', 'North', '2024-01', 5500);
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product C', 'South', '2024-01', 6000);
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product C', 'West', '2024-01', 4800);INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product C', 'North', '2024-02', 6500);
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product C', 'South', '2024-02', 7000);
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product C', 'West', '2024-02', 5800);INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product C', 'North', '2024-03', 7200);
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product C', 'South', '2024-03', 7800);
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product C', 'West', '2024-03', 6000);

样例一

-- 每个商品在不同的地区的总销售额SELECTproduct_name,region,sum( SALE_AMOUNT ) 
FROMsales_data 
GROUP BYproduct_name,region 
ORDER BYproduct_name,region 

这样是一行一行显示的,我们来转换为一列一列的显示。

-- 以商品为行 地区为列
SELECT* 
FROM( SELECT product_name, region, SALE_AMOUNT  FROM sales_data ) PIVOT ( sum( SALE_AMOUNT ) FOR region IN ( 'North', 'South', 'West' ) ) ORDER BY product_name 

-- 已地区为行 商品为列
SELECT* 
FROM( SELECT product_name, region, SALE_AMOUNT  FROM sales_data ) PIVOT ( sum( SALE_AMOUNT ) FOR product_name IN ( 'Product A', 'Product B', 'Product C' ) ) ORDER BY region 

多个聚合函数

每个商品在不同地区的销售总额,每个商品在不同地区的销售平均值

SELECT* 
FROM( SELECT product_name, region, SALE_AMOUNT FROM sales_data ) PIVOT ( sum( SALE_AMOUNT ),avg( SALE_AMOUNT ) FOR product_name IN ( 'Product A', 'Product B', 'Product C' ) ) ORDER BY region ;
-- > ORA-00918: 未明确定义列  
-- 这样直接写两个聚合函数在pivot里面是会报错。是因为两个聚合函数都没有使用,默认是使用in里面的值作为列名。
-- 所以当我们在使用多个聚合函数的时候需要至少一个为聚合函数指定 as
SELECT* 
FROM( SELECT product_name, region, SALE_AMOUNT  FROM sales_data ) PIVOT ( sum( SALE_AMOUNT ) as sum,avg( SALE_AMOUNT )as avg FOR product_name IN ( 'Product A', 'Product B', 'Product C' ) ) ORDER BY region ;

1705485249575(1)

注意

我这里用了select再给嵌套了一层,并且去掉了Name字段。

为什么?

我们使用select*试试。

SELECT* 
FROMsales_data PIVOT ( sum( SALE_AMOUNT ) AS sum, avg( SALE_AMOUNT ) AS avg FOR product_name IN ( 'Product A', 'Product B', 'Product C' ) ) 
ORDER BYregion

会发现想象的不太一样。😂

其实,这是因为pivot会以移出pivot_columnvalue_column后的字段组合当成唯一键(就类似以那几个字段group by)。

所以直接使用 pivot这个查询翻译成自然语言就是:查询每个地区,每个月的,商品的销售额。

多个FOR

也就是自己查询 对于product_name,region,销售额的总和。直接用列显示

SELECT* 
FROM( SELECT product_name, region, SALE_AMOUNT FROM sales_data ) PIVOT (sum( SALE_AMOUNT ) AS sum FOR ( product_name, region ) IN (( 'Product A', 'North' ) AS result1,( 'Product A', 'South' ) AS result2,( 'Product A', 'West' ) AS result3,( 'Product B', 'North' ) AS result4,( 'Product B', 'South' ) AS result5,( 'Product B', 'West' ) AS result16,( 'Product C', 'North' ) AS result7,( 'Product C', 'South' ) AS result8,( 'Product C', 'West' ) AS result9 ) )

总结

  1. pivot 函数是写在表名后面的,如果需要把源表过滤后再转换为列显示的需要嵌套子查询

  2. pivot 会以移出pivot_columnvalue_column剩下的字段组合成唯一键,每个唯一值占一行,查询每一组满足唯一键聚合函数的值。

  3. pivot 当使用多个聚合函数的时候至少需要指定一个 as

  4. pivotin 中是不支持使用子查询的,这是个缺点,但是也可以使用动态拼接的方式把想要转换为列的值拼接到这。

UNPIVOT

UNPIVOTPIVOT的相反操作。它用于将列数据转换为行数据。

将多列合并多为一列,合并为一列后自然需要多行才能展示全数据

语法

UNPIVOT(被合并列的列名 for 合并后的列名 in (被合并的列(),…))

SELECT* 
FROMtableName UNPIVOT ( fieldValueName FOR fieldName IN (  filedValue,...   ))
  • fieldValueName:被合并列的列名,可以随便起名称。
  • fieldName:合并后的列名,可以随便起名称。
  • filedValue:被合并的列。可以有多个。

准备

CREATE TABLE sales_by_region (product_name VARCHAR2(100),   -- 商品region_name VARCHAR2(50),	  -- 地区sales_q1 NUMBER,              -- 第一季度sales_q2 NUMBER,			  -- 第二季度sales_q3 NUMBER,              -- 第三季度sales_q4 NUMBER               -- 第四季度
);-- 商品 A 在不同地区的季度销售数据
INSERT INTO sales_by_region (product_name, region_name, sales_q1, sales_q2, sales_q3, sales_q4) VALUES ('Product A', 'North', 5000, 8000, 7000, 9000);
INSERT INTO sales_by_region (product_name, region_name, sales_q1, sales_q2, sales_q3, sales_q4) VALUES ('Product A', 'South', 7000, 7500, 8500, 9200);
INSERT INTO sales_by_region (product_name, region_name, sales_q1, sales_q2, sales_q3, sales_q4) VALUES ('Product A', 'West', 4500, 6000, 6200, 6900);-- 商品 B 在不同地区的季度销售数据
INSERT INTO sales_by_region (product_name, region_name, sales_q1, sales_q2, sales_q3, sales_q4) VALUES ('Product B', 'North', 6000, 7000, 7800, 8000);
INSERT INTO sales_by_region (product_name, region_name, sales_q1, sales_q2, sales_q3, sales_q4) VALUES ('Product B', 'South', 8000, 9000, 9200, 9500);
INSERT INTO sales_by_region (product_name, region_name, sales_q1, sales_q2, sales_q3, sales_q4) VALUES ('Product B', 'West', 5500, 6500, 6900, 7200);-- 商品 C 在不同地区的季度销售数据
INSERT INTO sales_by_region (product_name, region_name, sales_q1, sales_q2, sales_q3, sales_q4) VALUES ('Product C', 'North', 5500, 6500, 7200, 7800);
INSERT INTO sales_by_region (product_name, region_name, sales_q1, sales_q2, sales_q3, sales_q4) VALUES ('Product C', 'South', 6000, 7000, 7800, 8200);
INSERT INTO sales_by_region (product_name, region_name, sales_q1, sales_q2, sales_q3, sales_q4) VALUES ('Product C', 'West', 4800, 5800, 6000, 6500);

样例一

-- 普通查询
select * from sales_by_region

把四个季度的销售额合并到一个列中。

SELECT* 
FROMsales_by_region UNPIVOT (销售额 FOR 季度 IN (  sales_q1, sales_q2 , sales_q3, sales_q4 )

多个合并列

SELECT* 
FROMsales_by_region UNPIVOT ( (销售额1 ,销售额2 ) FOR 季度 IN ( ( sales_q1, sales_q2 ) as '上季度'  ,( sales_q3, sales_q4 ) as '下季度') );

上季度的销售额1 就相当于sales_q1,

上季度的销售额2 就相当于sales_q2,

下季度的销售额1 就相当于sales_q3,

下季度的销售额1 就相当于sales_q4,

有点绕,对应好即可。

总结

  1. unpivot函数也是写在表名后面,如果需要把源表过滤后再转换为列显示的需要嵌套子查询。(与pivot一样)
  2. unpivot会以移出被合并的列,然后将剩余的列组合成一个唯一值,每一个唯一值占一行。
  3. unpivot被合并的列的列名会在,fieldName中当做值来显示。
  4. 被合并的列可以通过 as 改变在fieldName显示的值。
  5. 大部分用法跟pivot一致,可以相互参考。
    ales_q3,

下季度的销售额1 就相当于sales_q4,

有点绕,对应好即可。

总结

  1. unpivot函数也是写在表名后面,如果需要把源表过滤后再转换为列显示的需要嵌套子查询。(与pivot一样)
  2. unpivot会以移出被合并的列,然后将剩余的列组合成一个唯一值,每一个唯一值占一行。
  3. unpivot被合并的列的列名会在,fieldName中当做值来显示。
  4. 被合并的列可以通过 as 改变在fieldName显示的值。
  5. 大部分用法跟pivot一致,可以相互参考。

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/243245.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

Flowable 生成流程图

/*** 生成流程图** param processId 任务ID*/ RequestMapping("/diagram/{processId}") public void genProcessDiagram(HttpServletResponse response,PathVariable("processId") String processId) {InputStream inputStream flowTaskService.diagram(p…

SpringCloud整合Zookeeper代替Eureka案例

文章目录 本期代码下载地址zookeeper简介zookeeper下载安装新建服务提供者测试 新建消费者测试 本期代码下载地址 地址:https://github.com/13thm/study_springcloud/tree/main/days4 zookeeper简介 zookeeper是一个分布式协调工具,可以实现注册中心功能 关闭Lin…

WampServer

开发笔记 推荐链接php无法保存SESSION问题部署SSL时候产生的问题 推荐链接 链接目录 php无法保存SESSION问题 php.ini文件和phpForApache.ini 文件 里面都有 对路径的控制,相关路径问题可能也需要进行修改,打开文件搜索wamp64或wamp 就可以看到了&…

线程池--JAVA

虽然线程是轻量级进程,但是如果当创建和销毁的的频率非常之高,那么它也就会消耗很多的资源。 而线程池就是用来优化线程频繁创建和销毁的场景,减少线程创建、销毁的频率。 ExecutorService JAVA标准库为我们实现了线程池,Execu…

windows11上安装虚拟机VMware

1、安装虚拟机(待补充) 第二步:安装VMware tools 实现windows文件上传到虚拟机中 1、安装好虚拟机后,查看虚拟机ip用Xshell连接虚拟机,并安装VMware tools(只有安装了VMware tools才能实现虚拟机和本机的文件共享。在…

无人机航迹规划(四):七种元启发算法(DBO、LO、SWO、COA、LSO、KOA、GRO)求解无人机路径规划(提供MATLAB代码)

一、七种算法(DBO、LO、SWO、COA、LSO、KOA、GRO)简介 1、蜣螂优化算法DBO 蜣螂优化算法(Dung beetle optimizer,DBO)由Jiankai Xue和Bo Shen于2022年提出,该算法主要受蜣螂的滚球、跳舞、觅食、偷窃和繁殖…

Python编辑开发---pycharm pro 2023 中文

PyCharm Pro 2023是一款功能强大的Python集成开发环境(IDE),旨在提高Python开发人员的生产力。它提供了智能代码编辑、实时代码分析和调试工具,支持版本控制和数据库工具,以及可扩展的插件系统。PyCharm Pro 2023可在多…

什么是区块链?

区块链 区块链 (英语:blockchain)是借由 密码学 与 共识机制 等技术建立,存储数据的 保证不可篡改和不可伪造的 分布式技术。 什么是区块 区块 就是将一批数据打包在一起,并且给打包出来的区块编号。第一个区块的编…

Kylin 安装novnc 远程访问

noVNC可以使用浏览器直接访问服务器,而不需要使用VNC客户端。 1.初始环境 关闭防火墙或允许IP访问本机 2.安装依赖 dnf install -y tigervnc-server git 3.git下载novnc git clone https://github.com/novnc/noVNC.git git clone https://gitee.com/yangyizhao…

Baumer工业相机堡盟工业相机如何通过NEOAPI SDK使用相机日志跟踪功能(C++)

Baumer工业相机堡盟工业相机如何通过NEOAPI SDK使用相机日志跟踪功能(C) Baumer工业相机Baumer工业相机NEOAPI SDK和短曝光功能的技术背景Baumer工业相机通过NEOAPI SDK使用相机日志跟踪功能1.引用合适的类文件2.通过NEOAPI SDK使用相机日志跟踪功能3.通…

如何用数据赋能社媒营销决策?

在数字化时代,越来越多的商家开始意识到数据分析对于改善经营的重要性。 传统决策更多依赖过往经验、商业直觉、他人的思路模板等方法,或者依靠描述性统计、简单的数据分析。在数字时代,则通过精细化数据分析,做出更明智的营销决策…

S2-08 ESP-IDF开发 : 存储

S2-06 和 S2-07 暂时先不发,课上没给同学们将,分别是 DMA 和 USB 章节,作为专项讲 存储 ESP32 系列芯片中,不同型号的芯片所携带的 ROM、SRAM、RCT SRAM、PSRAM 以及 Flash大小不同,他们的作用如下: SRAM…

2023年总结我所经历的技术大变革

📢欢迎点赞 :👍 收藏 ⭐留言 📝 如有错误敬请指正,赐人玫瑰,手留余香!📢本文作者:由webmote 原创📢作者格言:新的征程,我们面对的不仅…

如何使用支付宝沙箱环境本地配置模拟支付并结合内网穿透远程调试

文章目录 前言1. 下载当面付demo2. 修改配置文件3. 打包成web服务4. 局域网测试5. 内网穿透6. 测试公网访问7. 配置二级子域名8. 测试使用固定二级子域名访问 正文开始前给大家推荐个网站,前些天发现了一个巨牛的 人工智能学习网站, 通俗易懂&#xff…

基本查找(顺序查找)

基本查找/顺序查找 基本思想思路代码示例输出结果 ​ 说明:顺序查找适合于存储结构为数组或者链表。 基本思想 顺序查找也称为线形查找,属于无序查找算法。从数据结构线的一端开始,顺序扫描,依次将遍历到的结点与要查找的值相比…

linux基础学习(5):yum

yum是为了解决rpm包安装依赖性而产生的一种安装工具 1.yum源 1.1配置文件位置 yum源的配置文件在/etc/yum.repos.d/中 *Base源是网络yum源,也就是需要联网才能使用的yum源。默认情况下,系统会使用Base源 *Media源是光盘yum源,是本地yum源…

【Android12】Android Framework系列---Adb和PMS安装apk源码流程

Adb和PMS安装apk源码流程 adb install命令 通过adb install命令可以将apk安装到Android系统(注意:特定类型的apk,比如persist类型是无法通过adb安装的) 下述命令中adb解析install命令,并调用Android PackageManagerS…

Java实现大学计算机课程管理平台 JAVA+Vue+SpringBoot+MySQL

目录 一、摘要1.1 项目介绍1.2 项目录屏 二、功能模块2.1 实验课程档案模块2.2 实验资源模块2.3 学生实验模块 三、系统设计3.1 用例设计3.2 数据库设计3.2.1 实验课程档案表3.2.2 实验资源表3.2.3 学生实验表 四、系统展示五、核心代码5.1 一键生成实验5.2 提交实验5.3 批阅实…

【Android】app中阻塞的looper为什么可以响应touch事件

这里,我们考虑一个问题,Android中的应用是一个looper线程,没有任务时就阻塞着,其他线程通过handler调用等方式向主线程looper发送任务, 如果点击应用上的按钮,应用是怎么及时响应的呢, 是专门启…

Redis(01)——常用指令

基础指令 select 数字:切换到其他数据库flushdb:清空当前数据库flushall:清空所有数据库dbsize:查看数据库大小exists key1[key2 …]:判断当前的key是否存在keys *:查看所有的keyexpire key 时间&#xff…