从Oracle 表格行列转置说起第1/2页

































NO MONEY DAY
1 23 1
1 43 2
1 -45 3
2 42 1
2 -10 2
2 50 3
3 100 8

为了符合阅读习惯，最终报表希望是如下格式：





















NO MON TUE THR
1 23 43 -45
2 42 -10 50
3      

------------------------

咱们一步步来实现：

1.运用DECODE转换行为列

SQL：

SELECT NO,
    DECODE(DAY,1,MONEY,'') DAY1,
    DECODE(DAY,2,MONEY,'') DAY2,
    DECODE(DAY,3,MONEY,'') DAY3
FROM TEMP

结果：









































NO DAY1 DAY2 DAY3
1 23
1 43
1 -45
2 42
2 -10
2 50
3      

2.按NO字段分组，并更改列名

SQL：

SELECT NO, MAX(DAY1) MON, MAX(DAY2) TUE, MAX(DAY3) THR
 FROM (SELECT NO,
        DECODE(DAY, 1, MONEY,'') DAY1,
        DECODE(DAY, 2, MONEY,'') DAY2,
        DECODE(DAY, 3, MONEY,'') DAY3
     FROM TEMP)
 GROUP BY NO;

结果：





















NO MON TUE THR
1 23 43 -45
2 42 -10 50
3      

------------------------

重难点归纳：

1.DECODE缺省值设置

DECODE语法如下：decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)

如果缺省值由''（两个单引号）改为0，即SQL：

SELECT NO, MAX(DAY1) MON, MAX(DAY2) TUE, MAX(DAY3) THR
 FROM (SELECT NO,
        DECODE(DAY, 1, MONEY,0) DAY1,
        DECODE(DAY, 2, MONEY,0) DAY2,
        DECODE(DAY, 3, MONEY,0) DAY3
     FROM TEMP)
 GROUP BY NO;

结果如下（所有值为负与空值都被赋为0）：





















NO MON TUE THR
1 23 43 0
2 42 0 50
3 0 0 0

2.列缺省值设置（DAY值为8的显示为'undefined'）

SQL：

SELECT NO,MONEY,
    DECODE(DAY,1,'MON',2,'TUE',3,'THR','undefined') DAY
FROM TEMP

结果：

































NO MONEY DAY
1 23 MON
1 43 TUE
1 -45 THR
2 42 MON
2 -10 TUE
2 50 THR
3 100 undefined

3.行列转化在表单内数据量较大的情况下消耗较大

原因：

1.扫描目标数据时间开销大。

2.GROUP BY时，数据冗余带来的多行合并。

优点：

表结构稳定：DAY增加新值只需增加记录，无需新增新列！

下一页 decode()函數使用技巧

当前1/2页 12下一页阅读全文

赞 (0)

相关推荐

  • Oracle 数据库针对表主键列并发导致行级锁简单演示

    Oracle 数据库针对表主键列并发导致行级锁简单演示

    本文内容 •软件环境 •简单演示 Oracle 数据库并发导致行级锁 本文简单演示针对表主键并发导致的行级锁.并发是两个以上的用户对同样的数据进行修改(包括插入.删除和修改).锁的产生是因为并发.没有并发,就没有锁.并发的产生是因为系统需要,系统需要是因为用户需要. 软件环境 -------------------------------------------------------------------------------- •Windows 2003 Server •Oracle 1

  • oracle wm_concat 列转行 逗号分隔

    oracle wm_concat 列转行 逗号分隔

    [sql] 复制代码 代码如下: create table wlbtest1( username varchar2(2), deptID number ); create table wlbtest2( deptName varchar2(10), deptID number ); insert into wlbtest1 values ('a', 1); insert into wlbtest1 values ('b', 1); insert into wlbtest1 values ('c'

  • Oracle逗号分隔列转行实现方法

    Oracle逗号分隔列转行实现方法

    那么,现在如果给出一个权限编号,要去检索出用后这个权限的用户集合,就会需要在逗号分隔的多个权限编号中去匹配给出的这个权限编号.如果使用like去做,一则效率不高,二则匹配不精确.因此用到了逗号分隔列转行的方法.目前该方法只适合在oracle数据库中使用.该方法只需要sql语句就可以实现列转行. 下面给出该方法的示例: select a,b,c from(with test as (select 'aaa' a,'bbb' b,'1,2,3' c from dual)select a,b,subs

  • Oracle的数据表中行转列与列转行的操作实例讲解

    Oracle的数据表中行转列与列转行的操作实例讲解

    行转列 一张表 查询结果为 --行转列 select years,(select amount from Tb_Amount as A where month=1 and A.years=Tb_Amount.years)as m1, (select amount from Tb_Amount as A where month=2 and A.years=Tb_Amount.years)as m2, (select amount from Tb_Amount as A where month=3

  • Oracle实现行列转换的方法分析

    本文实例讲述了Oracle实现行列转换的方法.分享给大家供大家参考,具体如下: 1.固定列数的行列转换 如: student subject grade --------- ---------- -------- student1 语文 80 student1 数学 70 student1 英语 60 student2 语文 90 student2 数学 80 student2 英语 100 -- 转换为: 语文 数学 英语 student1 80 70 60 student2 90 80 10

  • ORACLE常用数值函数、转换函数、字符串函数

    本文更多将会介绍三思在日常中经常会用到的,或者虽然很少用到,但是感觉挺有意思的一些函数.分二类介绍,分别是: 著名函数篇 -经常用到的函数 非著名函数篇-即虽然很少用到,但某些情况下却很实用 注:N表示数字型,C表示字符型,D表示日期型,[]表示内中参数可被忽略,fmt表示格式. 单值函数在查询中返回单个值,可被应用到select,where子句,start with以及connect by 子句和having子句. (一).数值型函数(Number Functions) 数值型函数输入数字型参

  • Oracle CBO几种基本的查询转换详解

    在执行计划的开发过程中,转换和选择有这个不同的任务:实际上,在一个查询进行完语法和权限检查后,首先发生通称为"查询转换"的步骤,这里会进行一系列查询块的转换,然后才是"优选"(优化器为了决定最终的执行计划而为不同的计划计算成本从而选择最终的执行计划). 我们知道查询块是以SELECT关键字区分的,查询的书写方式决定了查询块之间的关系,各个查询块通常都是嵌在另一个查询块中或者以某种方式与其相联结:例如: 复制代码 代码如下: select * from employe

  • oracle中to_date详细用法示例(oracle日期格式转换)

    TO_DATE格式(以时间:2007-11-02 13:45:25为例) 1. 日期和字符转换函数用法(to_date,to_char) 复制代码 代码如下: select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual;   //日期转化为字符串  select to_char(sysdate,'yyyy')  as nowYear   from dual;   //获取时间的年  select to_char(sys

  • MySQL转换Oracle的需要注意的七个事项

    下面总结出MySQL转换Oracle的几点注意事项,希望对大家有所帮助. 1．自动增长的数据类型处理 MySQL有自动增长的数据类型,插入记录时不用操作此字段,会自动获得数据值.Oracle没有自动增长的数据类型,需要建立一个自动增长的序列号,插入记录时要把序列号的下一个值赋于此字段. CREATE SEQUENCE 序列号的名称 (最好是表名 序列号标记) INCREMENT BY 1 START WITH 1 MAXVALUE 99999 CYCLE NOCACHE: 其中最大的值按字段的长

  • 通过创建SQLServer 2005到 Oracle10g 的链接服务器实现异构数据库数据转换方案

    通过创建SQLServer 2005到 Oracle10g 的链接服务器实现异构数据库数据转换方案

    1.新建链接服务器  在图1中选中"链接服务器",右键选择"新建链接服务器",如图2,配置相关参数. 2.配置相关参数 在"常规"选项中,填写"链接服务器名称",访问接口选中 " Microsoft OLE DB Provider for Oracle"项.其中产品名称.数据源.访问接口字符串请填写配置的Oracle客户端在本地配置的net服务名.如图3所示. 在"安全性"项中,选中使用

  • 将mysql转换到oracle必须了解的50件事

    另外他们列出的这些区别有些是蛮有意义的,有些可能由于他们本人的MySQL DBA的身份,对Oracle的理解有些偏差,有些则有凑数的嫌疑. 但是总体上结合Baron Schwartz的列表可以让大家大概地了解到Oracle与MySQL的共同之处与差别分别体现在哪些方面.. Robert Treat 1. 有些人说Oracle没有限制,实际上他们说得可能是Oracle不支持LIMIT语法.不过可以rownum虚列来实现,不过会更加麻烦. 2. Oracle不支持偏移(offset)语法. 3. O

  • Oracle实现行转换成列的方法

    Oracle实现行转换成列的方法

    本文实例讲述了Oracle实现行转换成列的方法.分享给大家供大家参考,具体如下: 把行转成列 把学生表,成绩表,班级表,学科表 合并成一张成绩表效果如下: 创建表 --班级表 create table CLASS ( ID VARCHAR2(5) not null primary key, CLASSNAME VARCHAR2(10) ); --学生表 create table STUDENT ( ID VARCHAR2(10) not null primary key, NAME VARCHA

随机推荐