Oracle数据库实战:横表到纵表的高效转换技术详解

AI摘要

本文详细介绍了在Oracle数据库中如何使用Pivot、Unpivot操作进行横纵表转换,包括静态与动态SQL实现,以及利用CASE表达式和存储过程的方法。同时提供了性能优化建议,确保数据处理效率和安全性。

引言


        在数据分析和数据处理的过程中,我们经常遇到需要将横表结构的数据转换为纵表结构的需求,这种转换过程通常被称为“行列转换”。在Oracle数据库环境下,实现这一目标既可以通过SQL查询语句直接实现,也可以借助于PL/SQL存储过程等高级功能。

Oracle数据库实战:一键执行批量将横表转纵表(横向表转纵向表) 【→→→戳我跳转→】


一、横表与纵表的定义

        横表是指多列记录同一类属性的不同取值,而纵表则是将这类属性及其对应的取值展开为多行记录。例如,一张横表可能包含员工ID、项目1成绩、项目2成绩等多个字段,而在纵表中,每个项目及对应成绩会被拆分为独立的行记录。

二、Oracle SQL中的Pivot与Unpivot操作

1. Pivot操作

创建横表DDL和初始化数据DML

-- 创建横表 Employee_Projects     CSND-小小野猪
CREATE TABLE Employee_Projects (
    EmployeeID VARCHAR2(10),
    ProjectName VARCHAR2(30),
    Score NUMBER
);

-- 插入示例数据  CSND-小小野猪
INSERT INTO Employee_Projects VALUES ('Emp1', 'ProjectA', 85);
INSERT INTO Employee_Projects VALUES ('Emp1', 'ProjectB', 90);
INSERT INTO Employee_Projects VALUES ('Emp2', 'ProjectA', 92);
INSERT INTO Employee_Projects VALUES ('Emp3', 'ProjectA', 95);
INSERT INTO Employee_Projects VALUES ('Emp3', 'ProjectB', 88);
INSERT INTO Employee_Projects VALUES ('Emp3', 'ProjectC', 93);

COMMIT;

Oracle SQL从11g版本开始引入了Pivot关键字,用于将横表转换为纵表。其基本语法如下:

-- CSND-小小野猪
SELECT *
FROM your_table
PIVOT (
    AGGREGATE_FUNCTION(your_column)
    FOR pivot_column IN (value1, value2, ... valueN)
);

例如,假设有一个横表Employee_Projects,包含EmployeeID, ProjectName, Score三列,我们可以将其转换为每个员工每项项目的得分单独列为一行:

-- CSND-小小野猪
WITH Employee_Projects AS (
    SELECT 'Emp1' AS EmployeeID, 'ProjectA' AS ProjectName, 85 AS Score FROM DUAL
    UNION ALL
    SELECT 'Emp1', 'ProjectB', 90 FROM DUAL
    UNION ALL
    SELECT 'Emp2', 'ProjectA', 92 FROM DUAL
)
SELECT *
FROM Employee_Projects
PIVOT (
    MAX(Score)
    FOR ProjectName IN ('ProjectA' AS ProjectA_Score, 'ProjectB' AS ProjectB_Score)
);
2. Unpivot操作

创建纵表DDL和初始化DML语句

-- 创建一个虚拟的纵表数据集,这里只是为了演示Unpivot操作  CSND-小小野猪
CREATE TABLE VerticalTable AS
SELECT 'Emp1' AS EmpID, 'ProjectA' AS Attribute, 85 AS Value FROM DUAL UNION ALL
SELECT 'Emp1', 'ProjectB', 90 FROM DUAL UNION ALL
SELECT 'Emp2', 'ProjectA', 92 FROM DUAL UNION ALL
SELECT 'Emp3', 'ProjectA', 95 FROM DUAL UNION ALL
SELECT 'Emp3', 'ProjectB', 88 FROM DUAL UNION ALL
SELECT 'Emp3', 'ProjectC', 93 FROM DUAL;

COMMIT;

-- 使用Unpivot将纵表转为横表
SELECT EmpID, ProjectName, Score
FROM (
    SELECT EmpID, Attribute, Value
    FROM VerticalTable
)
UNPIVOT (
    Score FOR ProjectName IN (Attribute)
);

将纵表转为横表时,使用Unpivot操作,其基本语法如下:

-- CSND-小小野猪
SELECT *
FROM your_table
UNPIVOT (
    unpivoted_value
    FOR unpivot_column IN (column1, column2, ... columnN)
);

三、动态Pivot技术

对于不固定数量的项目,静态Pivot可能无法满足需求,此时可以采用动态SQL构建Pivot语句。这通常涉及到使用DBMS_SQL包或者EXECUTE IMMEDIATE命令构造并执行动态SQL。

-- CSND-小小野猪
DECLARE
  v_sql VARCHAR2(4000);
BEGIN
  -- 构建动态Pivot SQL语句
  v_sql := 'SELECT * FROM Employee_Projects
            PIVOT(MAX(Score) 
                 FOR ProjectName IN (' || list_of_projects || '))';   -- 这里的list_of_projects是表中多个的ProjectName

  -- 使用EXECUTE IMMEDIATE执行动态SQL
  EXECUTE IMMEDIATE v_sql;
END;

其中,list_of_projects需要提前获取并拼接成逗号分隔的字符串形式。

四、利用CASE表达式手动实现行列转换

        Oracle数据库实战:一键执行批量将横表转纵表(横向表转纵向表) 【→→→戳我跳转→】

在不支持Pivot操作的老版本Oracle数据库或特定场景下,可以采用CASE WHEN搭配GROUP BY的方式手动模拟Pivot效果:

-- CSND-小小野猪
SELECT EmployeeID,
       MAX(CASE WHEN ProjectName = 'ProjectA' THEN Score END) AS ProjectA_Score,
       MAX(CASE WHEN ProjectName = 'ProjectB' THEN Score END) AS ProjectB_Score
FROM Employee_Projects
GROUP BY EmployeeID;

五、利用存储过程实现横表转纵表

        Oracle数据库实战:一键执行批量将横表转纵表(横向表转纵向表) 【→→→戳我跳转→】

六、性能优化建议

  • 对于大数据量的行列转换操作,合理运用索引、分区等数据库优化技术可显著提高转换效率。
  • 尽量避免在转换过程中进行不必要的全表扫描,尽量减少中间临时表的使用。
  • 在进行动态Pivot时,注意SQL注入风险,并做好必要的变量清理和绑定工作。

结语

        横表到纵表的转换是数据库处理中的常见任务,Oracle提供了丰富的原生功能以及灵活的方法来实现这一目标。熟练掌握这些技巧有助于提升数据处理能力和效率,进而更好地服务于数据分析和报表生成等工作场景。

声明

本内容版权归属于CSDN-小小野猪,任何未经授权的复制、转载、传播、贩卖、转赠等均属违法行为,必将追究法律责任!!!

Oracle数据库实战:一键执行批量将横表转纵表(横向表转纵向表) 1、在日常开发中,业务提供的数据往往是横向数据,不符合开发设计的数据库表,若想转为纵表,手动操作效率太低,为此写了本方法。 2、适用于各类业务场景下的横表转纵表需求,只需要按照对应的表结构修改存储过程即可。 3、使用Oracle存储过程实现横表转纵表结构,无需繁琐的手动操作,一键执行,适合处理大批量数据转换任务,极大提升数据处理效率。 4、资源中提供了自己验证需要的业务数据、数据库表DDL脚本,可验证学习。 5、本内容版权归属于CSDN-小小野猪,任何未经授权的复制、转载、传播、贩卖、转赠等均属违法行为,必将追究法律责任!!! 立即下载

相关推荐

Oracle 数据显示 横表转纵表

横表转纵表亦可用与decode意义相似的case语句实现,原理同该语句,这里不再过多描述。

Oracle中横表转纵表

本文的横表存储的是特征数据,表的数据结构是经常会变化的,比如添加特征操作。 特征表设计为横表操作不灵活,别人在引用特征时也将受到这种变化影响,增加特征后也可能带来数据异常。此外,特征数量庞大之后,选用特征也会及其不便。 所以我们考虑将关联得到的横表,转化为纵表进行存储。 代码操作的数据库为:Oracle 值得注意的是横表转纵表后,为了统一数据类型,一般统一存储为string类型。 with t...

fwj_ntu的博客 7238

Dify DSL文件分享 Dify讨论群 大模型token资源等(持续收集更新)

基于LLMOps平台-Dify的一站式学习平台。包含不限于:Dify案例、DSL文件分享、模型接入、Dify交流讨论等各类资源分享。

同名公众号:dify实验室 3721

oracle 横表与纵表转化

一、横表和纵表 横表:通常指我们平时在数据库中建立的表,是一种普通的建表方式。 (主键、字段1、字段2......)如:时间、客户ID,基本通话费、漫游通话费,国内长途费、国际长途费....。 纵表:一般不多见,在表结构不确定的时候,如需增加字段的情况下的一种建表方式。 二、执行效率 横表:后台数据库管理员操作简单,直观,清晰可见,一目了然。但若要给横表中添加一个或者多个字段,就须重建表结构。 纵表:对于横表的弊端,纵表中只需要添加一条记录,就可以...

weixin_44543121的博客 2443

单细胞分析实战:当Seurat的SCTransform遇上Harmony,我的整合流程优化笔记

本文详细介绍了单细胞转录组数据分析中SCTransform与Harmony整合流程的优化策略。通过实战案例展示如何调整SCTransform参数、优化Harmony整合效果,并解决常见问题,帮助研究者获得更可靠的生物学发现。特别适合需要处理批次效应的单细胞数据分析人员。

weixin_42552716的博客 362

YOLOv8高级应用实战:多目标跟踪+实例分割+小样本检测(工业/安防场景落地指南)

安防监控中,需要跟踪多个行人的运动轨迹,而不只是框出“这是行人”;工业质检时,要精确分割出零件的缺陷区域(像素级),而不是只给一个粗略的矩形框;罕见场景下(如特殊故障检测、稀有动物识别),只能收集到几十张样本,普通训练方法精度惨不忍睹。我在落地工业安防和质检项目时,把YOLOv8的高级功能摸了个遍,踩过“多目标跟踪遮挡丢帧”“实例分割边缘模糊”“小样本检测过拟合”等一系列坑,最终总结出这套可直接复用的实战方案。多目标跟踪(ByteTrack)、实例分割、小样本检测。

专注于Python爬虫开发,分享爬虫技巧、项目实战与反爬经验,使用Scrapy、BeautifulSoup等工具,解决数据抓取难题。 753

SQL 横表和纵表的转换

横表就是普通的建表方式,如一个表结构为: 主键、字段1、字段2、字段3。。。 如果变成纵表后,则表结构为: 主键、字段代码、字段值。 而字段代码则为字段1、字段2、字段3。 具体为电信行业的例子。以用户帐单表为例一般出账时用户有很多费用客户,其数据一般存储为:时间,客户ID,费用科目,费用。这种存储结构一般称为纵表,其特点是行数多,字段少。 纵表在使用时由于行数多,统计用户数或对用户进行分档时还需...

weixin_45031351 6381

Matplotlib之bar3d画3D柱状图

3D柱状图通常用于展示数据集中不同分类项的数据大小或数量。与普通的柱状图相比,3D柱状图更适合展示数据的三个维度 3D 柱状图 按行/列涂颜色 柱加阴影、描黑边 自定义座标轴名、刻度标签、范围

我走的每一步都算数 1124

SQL 横表竖表转换

1.--行列转换原表: 姓名 科目 成绩 张三 语文 80 张三 数学 90 张三 物理 85 李四 语文 85 李四 物理 82 李四 英语 90 李四...

weixin_34019929的博客 113

【图像超分】论文复现:Pytorch实现SRCNN!保姆级复现教程!提供完整代码和最优权重模型(x2、x3、x4)!科研级论文曲线图!计算平均PSNR和SSIM!代码Windows和Linux都可用!

由于很多同学反应没用过jupyter notebook,更习惯用pycharm项目的形式跑代码。于是,本文将用pycharm项目的形式,重新复现SRCNN!【图像超分】论文复现:新手入门!Pytorch实现SRCNN,数据预处理、模型训练、测试、评估全流程详解,注释详细,简单修改就可以训练你自己的图像数据,有训练好的模型下载地址,随取随用。

主要更新底层视觉(去噪、超分等)相关的科研内容,形式为【论文精读】+【论文复现】 4711

【超分辨率(Super-Resolution)】关于【超分辨率重建】专栏的相关说明,包含专栏简介、专栏亮点、适配人群、相关说明、阅读顺序、超分理解、实现流程、研究方向、论文代码数据集汇总等

本专栏研究领域为【超分辨率重建】,涵盖图像超分、视频超分,实时超分,4K修复等方面。主要内容包括主流算法模型的论文精读、论文复现、毕业设计、涨点手段、调参技巧、论文写作、应用落地等方面。算法模型从SRCNN开始更新至今,一般是一篇论文精读对应一篇论文复现。论文精读详解理论,归化繁为简,归纳核心,积累词句,培养阅读论文和论文写作能力。论文复现依托Pytorch代码,实现完整的模型训练流程,总结调参方法,记录碰到的bug,论文插图可视化,培养读写代码能力、做实验的能力、以及应用落地能力。

主要更新底层视觉(去噪、超分等)相关的科研内容,形式为【论文精读】+【论文复现】 3万+

将Access转换成oracle,通过access 实现 Oracle ERP MPS 转换排产

时 间:2009-08-31 00:00:00作 者:摘 要:OracleERPMPS的调整对供应链计划起着至关重要的作用。而OracleERP中的MPS调整是对每一个料(编码)逐行不同日期进行调整其MPS数量。当编码多、滚动计划时间长的时候,就存在一个非常麻烦的问题——调整的时候无法对某个料进行直观的调整,以及对不同编码以不同分组进行汇总、调整;希望能通过针对每一个编码为一行记录,...

weixin_39568706的博客 209

【异常】XXL-job触发调度:address:http://192.168.x.x:x/ code:500 msg:xxl-rpc remoting error(connect timed out)

XXL-JOB调度任务失败分析:调度中心(10.254.234.80)与执行器(192.168.x.x)连接超时,返回500错误。主要原因是网络不通或执行器异常:1.网络不通可能由防火墙或网段隔离导致;2.执行器未启动或端口未监听;3.地址配置错误。解决方案:1.关闭代理使用内网;2.测试网络连通性和端口可用性;3.检查执行器进程状态和端口监听情况;4.核对执行器地址配置;5.必要时配置防火墙规则放行端口。

奶爸的编程之路,也就一周冷个三天 417

Unpivot:让列“站“成行的利器,也是性能陷阱的常客

Unpivot操作虽优雅但暗藏性能陷阱:将多列转为行时,数据库需对每列执行独立扫描,10列即10次全表扫描。其本质是UNION ALL的语法糖,在大数据场景下会累积巨额I/O开销。优化关键点包括:1)使用CTE预过滤减少重复扫描;2)控制旋转列数量;3)确保数据类型匹配避免隐式转换;4)亿级数据改用ETL处理。需特别注意Pivot与Unpivot并非可逆操作,聚合函数会导致原始数据丢失。合理运用Unpivot需权衡简洁性与执行成本,根据数据规模选择最优方案。

AI+全栈,AI+应用,实操落地,前沿技术 1万+

WINCC7.3破解文件

WINCC7.3硬件够破解文件,有需要的下载!WINCC7.3硬件够破解文件,有需要的下载!

假人有限元模型

假人模型,车辆碰撞假人有限元模型,适合做乘员安全的研究者利用

Oracle纵表转横

oracle纵表转横的案例

fm521521的博客 3203

Data-Structures-and-Algorithm-Analysis-in-Java.pdf

作者: Weiss, Mark A. 出版社: Addison Wesley Data Structures and Algorithm Analysis in Java is an “advanced algorithms” book that fits between traditional CS2 and Algorithms Analysis courses. In the old ACM Curriculum Guidelines, this course was known as CS7. This text is for readers who want to learn good programming and algorithm analysis skills simultaneously so that they can develop such programs with the maximum amo...

oracle横竖表互转

概述 一般我们数据存储在纵表里,类似于下图1。但是做报表时需要做成横表,类似于下图2。 竖(纵)表: 图1 图2 纵表转横表(做报表) 第一步:画表 SELECT name 姓名,no 考试编号,1 数学,1 语文,1 英语 from temp group by name,no order by no,name 第二步:填值 拿数学来说,通过姓名和考试编号来分组,则纵...

qq_33422712的博客 1268

Statistics and Machine Learning Toolbox

Statistics and Machine Learning Toolbox(带书签),MATLAB统计学习工具

oracle数据库纵横表变换

在我们平时使用数据库时,经常会发现有的表列数过多,为了提高效率,我们经常对要查询的表来纵横表变换。1.PIVOT函数PIVOT(任意聚合函数 FOR 列名 IN(类型)) 其中,【聚合函数】聚合的字段,是需要转化为列值的字段;【列名】是需要转化为列标识的字段,【类型】即是需要的结果展示,【类型】中可以指定别名; IN中还可以指定子查询。2、使用 MAX 和 DECODE 函数3、使用 CASE WHEN 和 GROUP BY1、使用对应的UNPIVOT函数函数说明:

qq_57278020的博客 1326

oracle 横竖互换,oracle横竖表互转

概述一般我们数据存储在纵表里,类似于下图1。但是做报表时需要做成横表,类似于下图2。竖(纵)表: 图1 图2纵表转横表(做报表)第一步:画表SELECT name 姓名,no 考试编号,1 数学,1 语文,1 英语 from temp group by name,no order by no,name第二步:填值拿数学来说,通过姓名和考试编号来分组,则纵表的多行数据就变成一组。如图3。 图3在这一...

weixin_27076351的博客 1907

【Oracle】横表变纵表

普通行列转换假设有张学生成绩表(tb)如下:Name Subject Result 张三 语文  74 张三 数学  83 张三 物理  93 李四 语文  74 李四 数学  84 李四 物理  94想变成 姓名 语文 数学 物理 ---------- ----------- ----------- ----------- 李四

Kida 的躺平小屋 2099

oracle 横向列变为纵向列

很多业务需求存储数据的时候都采取key/value的形式,展示时则需要key1,key2,key3....展示 效果如下: sql语句如下 SELECT ROUND(SUM(CASE WHEN DATA_ITEM_CODE='BACKN52' THEN DATA_ITEM_VALUE ELSE '0' END),2) AS 磨煤机入口压力,ROUND(SUM(CASE WHE...

左牵黄,右擎仓 3303

oracle横纵表相互转换

/*纵表转横表*/    CREATE GLOBAL TEMPORARY TABLE test2 ( uname VARCHAR2(10), subject VARCHAR2(10), score NUMBER ); INSERT INTO test2 VALUES('王五','语文',80); INSERT INTO test2 VALUES('李四',

wbbzj2006的专栏 7731

oracle服务端查询结果竖列展示

oracle查询结果竖列展示

码拉松 1759
上一篇: 使用START WITH和CONNECT BY PRIOR进行Oracle数据库中的递归查询,递归条件如何确定
下一篇: RSA加密解密签名加签验签RsaUtils工具类
小小野猪
博客等级 码龄9年 1万+粉丝 142原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

小小野猪

若恰好解决你的问题,望打赏哦。

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值
⌂ 换网址