精华内容
下载资源
问答
  • NULL 博文链接:https://lisanlai.iteye.com/blog/793404
  • oracle 列转行

    千次阅读 2020-03-28 11:43:30
    列转行 listagg() WITHIN GROUP () SELECT BUSIDATE,STKCODE,STKTYPE, TO_CHAR(LISTAGG(SHORTNAME, ',') WITHIN GROUP(ORDER BY (SHORTNAME))) AS FUNDNAMELIST FROM (TABLE_DATA) GROUP BY BUSIDATE,STKCODE,...

    PIVOT
    select * from 表/子查询 PIVOT 聚合函数[计算字段] for [统计字段] in ([值1], [值2], [值3] 李曼填充需要展示字段的哪些值)
    通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,并在必要时对最终输出中所需的任何其余列值执行聚合。

    SELECT [星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日]--这里是PIVOT第三步(选择行转列后的结果集的列)这里可以用“*”表示选择所有列,也可以只选择某些列(也就是某些天)
    FROM WEEK_INCOME --这里是PIVOT第二步骤(准备原始的查询结果,因为PIVOT是对一个原始的查询结果集进行转换操作,所以先查询一个结果集出来)这里可以是一个select子查询,但为子查询时候要指定别名,否则语法错误
    PIVOT
    (
     SUM(INCOME) for [week] in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])--这里是PIVOT第一步骤,也是核心的地方,进行行转列操作。聚合函数SUM表示你需要怎样处理转换后的列的值,是总和(sum),还是平均(avg)还是min,max等等。例如如果week_income表中有两条数据并且其week都是“星期一”,其中一条的income是1000,另一条income是500,那么在这里使用sum,行转列后“星期一”这个列的值当然是1500了。后面的for [week] in([星期一],[星期二]...)中 for [week]就是说将week列的值分别转换成一个个列,也就是“以值变列”。但是需要转换成列的值有可能有很多,我们只想取其中几个值转换成列,那么怎样取呢?就是在in里面了,比如我此刻只想看工作日的收入,在in里面就只写“星期一”至“星期五”(注意,in里面是原来week列的值,"以值变列")。总的来说,SUM(INCOME) for [week] in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])这句的意思如果直译出来,就是说:将列[week]值为"星期一","星期二","星期三","星期四","星期五","星期六","星期日"分别转换成列,这些列的值取income的总和。
    )TBL--别名一定要写
    

    列转行

    listagg() WITHIN GROUP ()

    SELECT BUSIDATE,STKCODE,STKTYPE,
    TO_CHAR(LISTAGG(SHORTNAME, ',') WITHIN GROUP(ORDER BY (SHORTNAME))) AS FUNDNAMELIST
    FROM (TABLE_DATA) 
    GROUP BY BUSIDATE,STKCODE,STKTYPE
    

    LISTAGG() 第一个属性代表要合并的字段,第二个属性代表用什么符号连接
    GROUP() 里面必须跟一个ORDER BY函数,可以升序(默认),也可以倒叙(加DESC)
    最后建议用TO_CHAR()把得到的结果转化为字符串

    展开全文
  • Oracle 列转行函数pivot

    2021-05-02 04:52:57
    [url=]行转列[/url]、列转行是我们经常会遇到的“诡异”需求。标准SQL没有提供此类型操作的支持函数,早期Oracle的版本中,我们也只能通过复杂的变通手段实现。在Oracle11g中,这种情况改变了,我们可以利用[url=]...

    作为数据库应用开发人员,我们有很大的精力应付在处理各种各样的数据类型,展现各种维度的报表上面。

    [url=]行转列[/url]、列转行是我们经常会遇到的“诡异”需求。标准SQL没有提供此类型操作的支持函数,早期Oracle的版本中,我们也只能通过复杂的变通手段实现。在Oracle11g中,这种情况改变了,我们可以利用[url=]pivot[/url]函数轻松实现行列互转并且聚合的查询需求。

    1、环境准备

    我们选择Oracle 11g进行实验。

    SQL> select * from v$version;

    BANNER

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

    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

    PL/SQL Release 11.2.0.1.0 - Production

    CORE11.2.0.1.0Production

    创建数据表T,进行有选择的数据筛选。

    SQL> create table t (owner varchar2(30), object_type varchar2(100), value number);

    Table created

    SQL> insert into t select owner, object_type, object_id from dba_objects where object_type in ('TABLE','INDEX');

    10386 rows inserted

    SQL> commit;

    Commit complete

    每个owner最多有两种类型的type值,每个owner和object_type组合包括多条记录。

    SQL> select * from t where rownum<5;

    OWNER OBJECT_TYP      VALUE

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

    SYS   TABLE              20

    SYS   INDEX              46

    SYS   TABLE              28

    SYS   TABLE              15

    要求以owner和object_type进行汇总,并且将TABLE和INDEX分别成列,显示汇总值。简而言之,就是一个owner一行记录,每列对应一种object_type取值。

    2、行转列处理

    按照抽丝剥茧的原则,我们聚合一次owner和object_type。

    --聚合汇总结

    SQL> select owner, object_type, sum(value) from t group by owner, object_type;

    OWNER                OBJECT_TYP SUM(VALUE)

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

    SCOTT                INDEX          146392

    PRITEST              TABLE          172006

    (篇幅原因,有省略......)

    DICT                 INDEX        18490973

    COMMON               TABLE         9076700

    63 rows selected

    下面就是避免不了的行转列操作了。在[url=]11g[/url]以前的版本中,我们可能需要借助专门的复杂SQL书写乃至存储过程才能解决。在11g中,我们可以使用pivot函数,一次性的将聚合操作和行转列操作完成。

    select *

    from t

    pivot

    (

    sum(value)  --聚合操作函数

    for object_type --行转列标准

    in ('TABLE','INDEX') --行转列列取值和顺序

    )

    上面的SQL就是pivot函数的基本格式。看起来非常简单,主要要定义三个部分的内容。

    ü 聚合列取值。需要告诉pivot函数进行转列的过程中,聚合操作的函数和处理对象;

    ü 行转列标准。依据那个列进行行转列;

    ü 列转行取值。因为要将数据行取值转成列,我们需要告诉Oracle那些取值成列,并且这些取值成列的过程中,列顺序是如何的;

    下面我们看执行结果。

    SQL> select *

    2  from t

    3  pivot

    4  (

    5     sum(value)

    6     for object_type

    7     in ('TABLE','INDEX')

    8  )

    9  ;

    OWNER                   'TABLE'    'INDEX'

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

    MDSYS                   8003137    7378327

    MELODY                   299266

    RMAN                    8292775   18094518

    OUTLN                      1351       1805

    (篇幅原因,有省略......)

    COMMON                  9076700   17161092

    34 rows selected

    处理成功,我们查看一下执行计划。

    SQL> explain plan for select *

    2  from t

    3  pivot

    4  (

    5     sum(value)

    6     for object_type

    7     in ('TABLE','INDEX')

    8  );

    Explained

    SQL> select * from table(dbms_xplan.display);

    PLAN_TABLE_OUTPUT

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

    Plan hash value: 3924414983

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

    | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

    |   0 | SELECT STATEMENT    |      |    34 |   612 |    13   (8)| 00:00:01 |

    |   1 |  HASH GROUP BY PIVOT|      |    34 |   612 |    13   (8)| 00:00:01 |

    |   2 |   TABLE ACCESS FULL | T    | 10386 |   182K|    12   (0)| 00:00:01 |

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

    9 rows selected

    从执行计划上看,Oracle为了pivot函数引入了一个专门操作pivot。执行计划中的hast group by pivot操作就是将数据转移到专门的处理区(猜想是PGA)进行流程化的处理。

    下面是使用autotrace处理过的情况。

    SQL> set autotrace traceonly;

    SQL> select *

    2  from t

    3  pivot

    4  (

    5     sum(value)

    6     for object_type

    7     in ('TABLE','INDEX')

    8  );

    已选择34行。

    执行计划

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

    Plan hash value: 3924414983

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

    | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

    |   0 | SELECT STATEMENT    |      |    34 |   612 |    13   (8)| 00:00:01 |

    |   1 |  HASH GROUP BY PIVOT|      |    34 |   612 |    13   (8)| 00:00:01 |

    |   2 |   TABLE ACCESS FULL | T    | 10386 |   182K|    12   (0)| 00:00:01 |

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

    统计信息

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

    196  recursive calls

    0  db block gets

    65  consistent gets

    42  physical reads

    0  redo size

    1503  bytes sent via SQL*Net to client

    441  bytes received via SQL*Net from client

    4  SQL*Net roundtrips to/from client

    3  sorts (memory)

    0  sorts (disk)

    34  rows processed

    注意在统计信息中,引用了3的sorts操作。

    应该说,pivot函数是我们可以找到比较好的行转列的操作方式了。在Oracle端,是要消耗一定的排序操作。当数据集合特别大的时候,我们可以猜测会有一些性能的退化(特别是发生sorts(disk))。Pivot作为标准Oracle函数,Oracle也会不断进行优化处理。所以,性能一般是可以接受的。

    3、进一步的需求

    在结果集合中,我们看到了结果标题如下:

    OWNER                   'TABLE'    'INDEX'

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

    MDSYS                   8003137    7378327

    MELODY                   299266

    Oracle是使用两个列值作为标题。那么我们结果如何引用这个列

    名称呢?

    SQL> select owner, 'TABLE','INDEX' from (

    2  select *

    3  from t

    4  pivot

    5  (

    6     sum(value)

    7     for object_type

    8     in ('TABLE','INDEX')

    9  )) where rownum<3;

    OWNER                'TABLE' 'INDEX'

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

    APEX_030200          TABLE   INDEX

    APPQOSSYS            TABLE   INDEX

    显然,Oracle将其识别为了字符串。应该怎么做呢?我们需要使用双引号。之所以Oracle会识别错误,就因为’’的常量识别优先级高于列名称优先级。我们需要让Oracle忽略这个特点,就使用双引号。

    SQL> select owner, 'TABLE' PRI_TYP,"'TABLE'" PRI_AMT,'INDEX' SEC_TYP,"'INDEX'" SEC_AMT from (

    2  select *

    3  from t

    4  pivot

    5  (

    6     sum(value)

    7     for object_type

    8     in ('TABLE','INDEX')

    9  )) where rownum<3;

    OWNER                PRI_TYP    PRI_AMT SEC_TYP    SEC_AMT

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

    APEX_030200          TABLE     25757431 INDEX     67809456

    APPQOSSYS            TABLE        25887 INDEX

    实现成功!

    4、结论

    在现实工作中,我们遇到的需求千奇百怪。在选择工具的时候,要有选择类库、预定义提供的方法函数,不要轻易的选择自定义方法。因为对现成的类库和产品来说,它在实现这个方法的时候倾注了很大心血和能力在其中,其效率一定是比我们自己写的要强很多。这也就要求我们需要不断关注新特性,新方法和改进,不断的学习和进步。

    展开全文
  • oracle列转行方法总结

    2021-05-03 08:20:47
    方法一:-------------------------------------------------------------------Muti-row to line(col2row)----------------------------------------------------------------create or replace type str_tab is ...

    方法一:

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

    ---Muti-row to line(col2row)

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

    create or replace type str_tab is table of varchar2(20);

    /

    grant all on str_tab to public;

    create public synonym str_tab for str_tab;

    create or replace function col2row(pv in str_tab) return varchar2

    is

    ls varchar2(4000);

    begin

    for i in 1..pv.count loop

    ls := ls || pv(i);

    end loop;

    return ls;

    end;

    /

    grant execute on col2row to public;

    create public synonym col2row for col2row;

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

    --multi column,convert one column base on another column, for example

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

    create table t(id number,name varchar2(10));

    insert into t values(1,'Joan');

    insert into t values(1,'Jack');

    insert into t values(1,'Tom');

    insert into t values(2,'Rose');

    insert into t values(2,'Jenny');

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

    SQL(c3dev)>select * from t;

    ID NAME

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

    1 Joan

    1 Jack

    1 Tom

    2 Rose

    2 Jenny

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

    --column to row

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

    SQL(c3dev)>column names format a80;

    SQL(c3dev)>set line 120

    SQL(c3dev)>select t0.id,

    2     col2row(cast(multiset(select name from t where t.id = t0.id) as str_tab)) names

    3  from (select distinct id from t) t0;

    ID NAMES

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

    &nbs

    展开全文
  • oracle列转行函数

    2018-04-18 09:34:59
    SELECT TRIM(',' FROM SYS.STRAGG(A_NAME||NVL2(A_NAME,',','')))as nams FROM A_TEMP
  • oracle列转行

    2014-12-08 16:03:43
    oracle列转行的方法,有些时候页面显示要通过后台的列转行去实现,我们可以直接从sql中解决后台很复杂的问题
  • Oracle列转行的函数

    2021-07-16 11:58:43
    listagg是一个列转行的函数,返回一个varchar2的字符串,measure_expr为要转换的列,可以多列,delimiter是measure_expr之间拼接的分隔符, 可以没有,within group 中的order_by_caluse是measure_expr中值的排序。...

    listagg是一个列转行的函数,返回一个varchar2的字符串,measure_expr为要转换的列,可以多列,delimiter是measure_expr之间拼接的分隔符,
    可以没有,within group 中的order_by_caluse是measure_expr中值的排序。over后的query_partition_caluse属于分析部分,即可以对查询的结果进行分组。
    如:
    SELECT LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date, last_name) “Emp_list”, MIN(hire_date) “Earliest”
    FROM employees WHERE department_id = 30;

    展开全文
  • oracle 列转行、行转列

    2020-12-21 03:35:06
    行转列:PIVOT列转行:UNPIVOT这两个是在oracle11g上面新增的函数。下面举例说明用法。PIVOT:学生成绩表,原数据:select class_name, student_name, course_type, result, created_datefrom class_tmp_2;每个同学...
  • Oracle列转行函数使用

    2020-12-23 06:24:42
    这就想到oracle列转行函数vm_concat。可以用类似这种格式wm_concat(a || '(' || b || ')'),a表示用户名字段,b表示账号字段。例子:select to_char(wm_concat(bs.user_name || '(' || bs.user_...
  • oracle列转行 listagg

    2021-06-07 21:24:39
    oracle 列转行listagg实现方式 当结果中只有要转化的那一列时候 select listagg(t.shopName, ',') within group(order by t.idCno) from( select idCno, shopName from shop where idCno in (select p.idCno ...
  • Oracle列转行unpivot函数的使用

    千次阅读 2020-12-29 15:41:05
    Oracle列转行unpivot函数的使用新的改变功能快捷键合理的创建标题,有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右...
  • 本博客记录一下Oracle列转行函数在Oracle11的一些不兼容问题,vm_concat在一些业务场景是必须的。不过这个函数使用要谨慎,底层实现应该也是group by等等实现的,性能并不是特别好。这个函数在Oracle12是没有的,在...
  • oracle wm_concat函数,用于列转行,逗号分隔本文将详细介绍此功能的应用
  • 右边是转后数据,数不确定。 主要分3步 --1-----------------------创建存储过程------------------------- CREATE OR REPLACE PROCEDURE P_TEST IS --定义变量 拼接语句 V_SQL VARCHAR2(2000); --...
  • 本博客记录一下Oracle列转行函数在Oracle11的一些不兼容问题,vm_concat在一些业务场景是必须的。不过这个函数使用要谨慎,底层实现应该也是group by等等实现的,性能并不是特别好。这个函数在Oracle12是没有的,在...
  • Oracle列转行

    千次阅读 2018-05-12 07:44:41
    SELECT cp.id,cp.docstatus, REGEXP_SUBSTR(CP.categoryids, ‘[^,]+’, 1, L) AS categoryids,cp.isdelete FROM docbase CP, (SELECT LEVEL L FROM DUAL CONNECT BY LEVEL &lt;= 1000) ...
  • Oracle列转行函数 Listagg() 语法详解及应用实例

    万次阅读 多人点赞 2017-01-03 22:16:54
    工作中用到一段比较复杂的...说简单点,listagg()函数可以实现多记录聚合为一条记录,从而实现数据的压缩、致密化(data densification)。以下内容转载自http://dacoolbaby.iteye.com/blog/1698957,SQL脚本做了...
  • oracle列转行和以逗号截取字符串的混合使用 最近,在写存储过程给前端返回值的时候,用到了行转列和截取字符串的混合使用 要转换的数据 想要实现的数据 实现SQL ```sql select OLD_REEL,OLD_QTY,substr(REEL,0,...
  • PAGE / NUMPAGES 列转行 主要讨论sys_connect_by_path的使用方法 1带层次关系 SQL> create table dept(deptno number,deptname varchar2(20,mgrno number;文档收集自网络仅用于个人学习 Table created. SQL> insert ...
  • 例子: SELECT REGEXP_SUBSTR('A,B,C,D', '[^,]+', 1, LEVEL, 'i') AS ID , REGEXP_SUBSTR('1,2,3,4', '[^,]+', 1, LEVEL, 'i') AS TEXT FROM DUAL CONNECT BY LEVEL

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 3,491
精华内容 1,396
关键字:

oracle列转行