精华内容
下载资源
问答
  • oracle 行转列,多行转

    千次阅读 2018-11-14 10:35:23
    问题描述:  应公司要求,设计功能,个ID,对应不同的值,展示的时候不同的值拼接展示,如何实现;... 2) Oracle 自带的个函数 wm_concat 函数 非常给力; 上代码 --测试表1 create table ceshi1(ID var...

    问题描述:

       应公司要求,设计功能,一个ID,对应不同的值,展示的时候不同的值拼接展示,如何实现;

     

    解决思路:

        1) 拼接字符串,想到了 oracle  Function(),这样肯定能实现,但是比较麻烦;

         2) Oracle 自带的一个函数 wm_concat 函数 非常给力;

    上代码

    
    --测试表1
    create table ceshi1(ID varchar2(10),MS varchar2(100));
    --测试表2
    create table ceshi2(ID varchar2(10),MS_1 varchar2(100));
    
    
    
    
    insert into ceshi1 (ID, MS)
    values ('1', '测试');
    
    insert into ceshi1 (ID, MS)
    values ('2', '测试2');
    
    
    insert into ceshi2 (ID, MS_1)
    values ('1', '展示1');
    
    insert into ceshi2 (ID, MS_1)
    values ('1', '展示2');
    
    
    ---强大的功能即将展示
    
    SELECT T.ID,
           (SELECT WM_CONCAT(T2.MS_1) FROM CESHI2 T2 WHERE T2.ID = T.ID) AS TT
      FROM CESHI1 T
    
    

    搞定!收工~

    展开全文
  • oracle行转列(动态行转不定)

    千次阅读 2017-06-09 16:51:19
    ---------------------------------------------------------------行转列的存储过程 CREATE OR REPLACE PROCEDURE P_TEST IS  V_SQL VARCHAR2(2000);  CURSOR CURSOR_1 IS SELECT DISTINCT T.XCLCK FROM ...

    原文链接

    /*物料 需要数量 需要仓库 现存量仓库 现存量仓库数量 批次 
    A1 2 C1 C1         20         123 
    A1 2 C1 C2         30         111 
    A1 2 C1 C2         20         222 
    A1 2 C1 C3         10         211 
    A2 3 C4 C1         40         321 
    A2 3 C4 C4         50         222 
    A2 3 C4 C4         60         333 
    A2 3 C4 C5         70         223 
    我需要把上面的查询结果转换为下面的。
    物料 需要数量 需要仓库 C1 C2 C3 C4 C5 
    A1 2 C1 20 50 10 0 0 
    A2 3 C4 40 0 0 110 70 
    */ 
    ---------------------------------------------------------------建表
    ----------------判断表是否存在
    declare num number; 
    begin 
        select count(1) into num from user_tables where table_name='TEST';
        if num>0 then 
          execute immediate 'drop table TEST';
        end if;
    end;
    ----------------建表
    CREATE TABLE TEST(
        WL VARCHAR2(10),
        XYSL INTEGER,
        XYCK VARCHAR2(10),
        XCLCK VARCHAR2(10),
        XCLCKSL INTEGER,
        PC INTEGER
    );
    ----------------第一部分测试数据
    INSERT INTO TEST VALUES('A1', 2, 'C1', 'C1' ,        20,         123); 
    INSERT INTO TEST VALUES('A1', 2, 'C1', 'C2' ,        30,         111); 
    INSERT INTO TEST VALUES('A1', 2, 'C1', 'C2' ,        20,         222); 
    INSERT INTO TEST VALUES('A1', 2, 'C1', 'C3' ,        10,         211); 
    INSERT INTO TEST VALUES('A2', 3, 'C4', 'C1' ,        40,         321); 
    INSERT INTO TEST VALUES('A2', 3, 'C4', 'C4' ,        50,         222); 
    INSERT INTO TEST VALUES('A2', 3, 'C4', 'C4' ,        60,         333); 
    INSERT INTO TEST VALUES('A2', 3, 'C4', 'C5' ,        70,         223);
    COMMIT;
    --select * from test;
    ---------------------------------------------------------------行转列的存储过程
    CREATE OR REPLACE PROCEDURE P_TEST IS
      V_SQL VARCHAR2(2000);
      CURSOR CURSOR_1 IS SELECT DISTINCT T.XCLCK FROM TEST T ORDER BY XCLCK; 
        
        BEGIN
          V_SQL := 'SELECT WL,XYSL,XYCK';

          FOR V_XCLCK IN CURSOR_1
          LOOP
            V_SQL := V_SQL || ',' || 'SUM(DECODE(XCLCK,''' || V_XCLCK.XCLCK ||
                     ''',XCLCKSL,0)) AS ' || V_XCLCK.XCLCK;
          END LOOP;
          
          V_SQL := V_SQL || ' FROM TEST GROUP BY WL,XYSL,XYCK ORDER BY WL,XYSL,XYCK';
          --DBMS_OUTPUT.PUT_LINE(V_SQL);
          V_SQL := 'CREATE OR REPLACE VIEW RESULT  AS '||  V_SQL;
          --DBMS_OUTPUT.PUT_LINE(V_SQL);
          EXECUTE IMMEDIATE V_SQL;
        END;
    ----------------------------------------------------------------结果
    ----------------执行存储过程,生成视图
    BEGIN
      P_TEST;               
    END;
    ----------------结果
    SELECT * FROM RESULT T;
    WL                                            XYSL XYCK               C1         C2         C3         C4         C5
    ---------- --------------------------------------- ---------- ---------- ---------- ---------- ---------- ----------
    A1                                               2 C1                 20         50         10          0          0
    A2                                               3 C4                 40          0          0        110         70

    ----------------第二部分测试数据
    INSERT INTO TEST VALUES('A1', 2, 'C1', 'C6' ,        20,         124); 
    INSERT INTO TEST VALUES('A2', 2, 'C1', 'C7' ,        30,         121); 
    INSERT INTO TEST VALUES('A3', 2, 'C1', 'C8' ,        20,         322); 
    COMMIT;
    ----------------报告存储过程,生成视图
    BEGIN
      P_TEST;               
    END;
    ----------------结果
    SELECT * FROM RESULT T;
    WL     XYSL XYCK          C1       C2         C3         C4         C5         C6         C7         C8
    ----- ----- -------- ------- -------- ---------- ---------- ---------- ---------- ---------- ----------
    A1        2 C1            20       50         10          0          0         20          0          0
    A2        2 C1             0        0          0          0          0          0         30          0
    A2        3 C4            40        0          0        110         70          0          0          0
    A3        2 C1             0        0          0          0          0          0          0         20
    --------------- 删除实体
    DROP VIEW RESULT;
    DROP PROCEDURE P_TEST;
    DROP TABLE TEST;  


    展开全文
  • 多行字符串 这个比较简单,用||或concat函数可以实现 ? 1 ... select concat(id,username) str from ... 字符串转多列 实际上就是拆分字符串的问题,可以使用 substr、instr、regexp_substr函数方式 ...

    多行转字符串

    这个比较简单,用||或concat函数可以实现

    ?

    1

    select concat(id,username) str from app_userselect id||username str from app_user

    字符串转多列

    实际上就是拆分字符串的问题,可以使用 substr、instr、regexp_substr函数方式

    字符串转多行

     

    使用union all函数等方式

    wm_concat函数

    首先让我们来看看这个神奇的函数wm_concat(列名),该函数可以把列值以","号分隔起来,并显示成一行,接下来上例子,看看这个神奇的函数如何应用准备测试数据

     

     

    ?

    1

    create table test(id number,name varchar2(20));insert into test values(1,'a');insert into test values(1,'b');insert into test values(1,'c');insert into test values(2,'d');insert into test values(2,'e');

     

    效果1 : 行转列 ,默认逗号隔开

     

     

    ?

    1

    select wm_concat(name) name from test;

    /

     

    效果2: 把结果里的逗号替换成"|"

     

    ?

    1

    select replace(wm_concat(name),',','|') from test;

    /

     

    效果3: 按ID分组合并name

    ?

    1

    select id,wm_concat(name) name from test group by id;

    /
    sql语句等同于下面的sql语句

    ?

    1

    -------- 适用范围:8i,9i,10g及以后版本  ( MAX + DECODE )select id, max(decode(rn, 1, name, null)) || max(decode(rn, 2, ',' || name, null)) || max(decode(rn, 3, ',' || name, null)) str       from (select id,name,row_number() over(partition by id order by name) as rn from test) t group by id order by 1;      -------- 适用范围:8i,9i,10g及以后版本 ( ROW_NUMBER + LEAD )select id, str from (select id,row_number() over(partition by id order by name) as rn,name || lead(',' || name, 1) over(partition by id order by name) ||    lead(',' || name, 2) over(partition by id order by name) || lead(',' || name, 3) over(partition by id order by name) as str from test) where rn = 1 order by 1;  -------- 适用范围:10g及以后版本 ( MODEL )select id, substr(str, 2) str from test model return updated rows partition by(id) dimension by(row_number() over(partition by id order by name) as rn) measures (cast(name as varchar2(20)) as str) rules upsert iterate(3) until(presentv(str[iteration_number+2],1,0)=0) (str[0] = str[0] || ',' || str[iteration_number+1]) order by 1;              -------- 适用范围:8i,9i,10g及以后版本 ( MAX + DECODE )select t.id id,max(substr(sys_connect_by_path(t.name,','),2)) str from (select id, name, row_number() over(partition by id order by name) rn from test) t       start with rn = 1 connect by rn = prior rn + 1 and id = prior id group by t.id;</span>

    懒人扩展用法:

    案例: 我要写一个视图,类似"create or replace view as select 字段1,...字段50 from tablename" ,基表有50多个字段,要是靠手工写太麻烦了,有没有什么简便的方法? 当然有了,看我如果应用wm_concat来让这个需求变简单,假设我的APP_USER表中有(id,username,password,age)4个字段。查询结果如下

     

     

    ?

    1

    /** 这里的表名默认区分大小写 */ select 'create or replace view as select '|| wm_concat(column_name) || ' from APP_USER' sqlStr from user_tab_columns where table_name='APP_USER';

    /

     

    利用系统表方式查询

     

    ?

    1

    select * from user_tab_columns

    /
     

     

     

    Oracle 11g 行列互换 pivot 和 unpivot 说明

     

    在Oracle 11g中,Oracle 又增加了2个查询:pivot(行转列) 和unpivot(列转行)

    参考:http://blog.csdn.net/tianlesoftware/article/details/7060306、http://www.oracle.com/technetwork/cn/articles/11g-pivot-101924-zhs.html

     

    google 一下,网上有一篇比较详细的文档:http://www.oracle-developer.net/display.php?id=506

    pivot 列转行

    测试数据 (id,类型名称,销售数量),案例:根据水果的类型查询出一条数据显示出每种类型的销售数量。

     

    ?

    1

    create table demo(id int,name varchar(20),nums int);  ---- 创建表insert into demo values(1, '苹果', 1000);insert into demo values(2, '苹果', 2000);insert into demo values(3, '苹果', 4000);insert into demo values(4, '橘子', 5000);insert into demo values(5, '橘子', 3000);insert into demo values(6, '葡萄', 3500);insert into demo values(7, '芒果', 4200);insert into demo values(8, '芒果', 5500);

    /

     

    分组查询 (当然这是不符合查询一条数据的要求的)

    ?

    1

    select name, sum(nums) nums from demo group by name

     

    /

    行转列查询

     

    ?

    1

    select * from (select name, nums from demo) pivot (sum(nums) for name in ('苹果' 苹果, '橘子', '葡萄', '芒果'));


    /

     

    注意: pivot(聚合函数 for 列名 in(类型)) ,其中 in(‘’) 中可以指定别名,in中还可以指定子查询,比如 select distinct code from customers

    当然也可以不使用pivot函数,等同于下列语句,只是代码比较长,容易理解

     

    ?

    1

    select * from select sum(nums) 苹果 from demo where name='苹果'),(select sum(nums) 橘子 from demo where name='橘子'),       (select sum(nums) 葡萄 from demo where name='葡萄'),(select sum(nums) 芒果 from demo where name='芒果');

    unpivot 行转列

    顾名思义就是将多列转换成1列中去
    案例:现在有一个水果表,记录了4个季度的销售数量,现在要将每种水果的每个季度的销售情况用多行数据展示。

     

    创建表和数据

     

    ?

    1

    create table Fruit(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int);insert into Fruit values(1,'苹果',1000,2000,3300,5000);insert into Fruit values(2,'橘子',3000,3000,3200,1500);insert into Fruit values(3,'香蕉',2500,3500,2200,2500);insert into Fruit values(4,'葡萄',1500,2500,1200,3500);select * from Fruit

    /

     

    列转行查询

     

    ?

    1

    select id , name, jidu, xiaoshou from Fruit unpivot (xiaoshou for jidu in (q1, q2, q3, q4) )

    注意: unpivot没有聚合函数,xiaoshou、jidu字段也是临时的变量
    /

     

    同样不使用unpivot也可以实现同样的效果,只是sql语句会很长,而且执行速度效率也没有前者高

     

    ?

    1

    select id, name ,'Q1' jidu, (select q1 from fruit where id=f.id) xiaoshou from Fruit funionselect id, name ,'Q2' jidu, (select q2 from fruit where id=f.id) xiaoshou from Fruit funionselect id, name ,'Q3' jidu, (select q3 from fruit where id=f.id) xiaoshou from Fruit funionselect id, name ,'Q4' jidu, (select q4 from fruit where id=f.id) xiaoshou from Fruit f

     

    XML类型

    上述pivot列转行示例中,你已经知道了需要查询的类型有哪些,用in()的方式包含,假设如果您不知道都有哪些值,您怎么构建查询呢?

    pivot 操作中的另一个子句 XML 可用于解决此问题。该子句允许您以 XML 格式创建执行了 pivot 操作的输出,在此输出中,您可以指定一个特殊的子句 ANY 而非文字值

    示例如下:

     

    ?

    1

    select * from (   select name, nums as "Purchase Frequency"   from demo t)                              pivot xml (   sum(nums) for name in (any))

    /
    如您所见,列 NAME_XML 是 XMLTYPE,其中根元素是 <PivotSet>。每个值以名称-值元素对的形式表示。您可以使用任何 XML 分析器中的输出生成更有用的输出。

     

     

     

    /

     

    结论

     

    Pivot 为 SQL 语言增添了一个非常重要且实用的功能。您可以使用 pivot 函数针对任何关系表创建一个交叉表报表,而不必编写包含大量 decode 函数的令人费解的、不直观的代码。同样,您可以使用 unpivot 操作转换任何交叉表报表,以常规关系表的形式对其进行存储。Pivot 可以生成常规文本或 XML 格式的输出。如果是 XML 格式的输出,您不必指定 pivot 操作需要搜索的值域。

     

    转自:https://www.cnblogs.com/yewg/p/5510594.html

    展开全文
  • oracle 多行转一列一列转多行

    万次阅读 2015-06-12 17:07:31
    、多行转一列 select id, vnum, to_char(wmsys.wm_concat(vname)) c  from tab_test group by id,vnum; 执行上述sql后,如下: 二、一列转多行 with a as (select '/ABC/AA/AD/ABD/JI/CC/ALSKD/AL
    有一个表,表名为tab_test,如下:
    


    一、多行转一列
    select id, vnum, to_char(wmsys.wm_concat(vname)) c 
    from tab_test group by id,vnum;
    执行上述sql后,如下:


    二、一列转多行
    with a as (select '/ABC/AA/AD/ABD/JI/CC/ALSKD/ALDKDJ' id from dual)
    select regexp_substr(id,'[^/]+',1,rownum) id from a
    connect by rownum <= length(regexp_replace(id,'[^/]+'))
    执行上述sql后如下:



    转载自:http://blog.163.com/fushahui_1988@126/blog/static/82879994201192844355174/

    展开全文
  • Oracle 行转列转行

    万次阅读 2016-05-16 18:37:58
    在生成报表的时候,很时候取值固定或者有限的几个值时,进行转列分析。如下: facility取值只有四种,每种一列,遇到相同facility求和。如下图: 实现方式 DECODE 利用DECODE函数、聚合函数SUM、GROUP ...
  • HH 终风且暴,顾我则笑,谑浪笑敖,中心是悼。 终风且霾,惠然肯来,莫往莫来,悠悠我思。 博客园 首页 ...Oracle行转列转行的Sql语句总结 多行转字符串 这个比较简单,用||或concat函数...
  • Oracle 实现行转列

    千次阅读 2017-07-30 11:11:00
    效果1 : 行转列 ,默认逗号隔开 1 select wm_concat( name ) name from test; 效果2: 把结果里的逗号替换成"|" 1 select replace (wm_concat( name ), ',' , '|' ) ...
  • Oracle行转列转行的Sql语句总结

    千次阅读 2018-05-29 10:37:11
    号分隔起来,并显示成一行,接下来上例子,看看这个神奇的函数如何应用准备测试数据 SQL Code 123456 create table test(id number,name varchar2(20));insert into test values(1,'a');insert into t...
  • 特点:只能拼接两个字符串,也可以使用嵌套的方式拼接个。 嵌套方法: 方法2 :|| 拼接 :   现有一张省级架构表,需要把字段归为 “100001 省公司” 的样式:   || 拼接:相比concat函数使用更...
  • Oracle SQL函数pivot、unpivot转置函数实现行转列转行
  • 多行字符串这个比较简单,用||或concat函数可以实现 SQL Code 12 select concat(id,username) str from app_userselect id||username str from app_user字符串转多列实际上就是拆分字符串的问题,可以...
  • oracle查询多列单列去重

    千次阅读 2019-04-11 17:45:30
    oracle查询多列单列去重 最近有个查询,需要针对查询结果中的某一列进行去重,而又要得到想要的其他开始就想到distinct去重,但是distinct去重的话针对查询结果为单列时有效,如果查询的结果包括多列...
  • Oracle数据库

    千次阅读 2019-07-29 20:33:09
    学习主题:Oracle数据库的使用 学习目标: 1 掌握数据库相关概念 2 掌握oracle的安装,认识oracle的目录以及系统用户 3 掌握oracle常用命令 1.数据库相关概念 (1)请解释什么是DB, DBMS, DBAS, DBA, DBS? DB数据库,...
  • 刚刚接触数据库的真正应用,刚开始有很困难,在网上找答案也没有十分靠谱的结果,现在我把自己的经验总结下来,希望能给广大小鲜肉一点灵感,不胜荣幸。 比如要实现如下的功能,将表中的数据检索出来,放入到下列...
  • Oracle转换Postgres

    千次阅读 2019-10-16 21:38:06
    首先需要对Oracle和PostgreSQL的SQL都比较熟悉。其理解的越详细就越具有优势,本文帮助读者迅速理解这两类SQL的区别是什么。 如果因ACS/pg而需要将Oracle移植到PG,那么就需要熟悉AOLserver Tcl,尤其是SOLserver...
  • Oracle行转列的方法—wmsys.wm_concat

    千次阅读 2010-11-29 17:38:00
    关于oracle合并个小记录 个工作中遇到的ORACLE合并合并结果排序的记录。本来简单的认为使用wmsys.wm_concat函数就,结果出了一些小问题,解决起来还是挺折腾的,认真记录一下:表eims_gwjk_dbcb_log...
  • Oracle中匹配个字段中有个字符匹配的解决方法1、问题例子 1、问题例子 现在需要对表中的(如下图中红色框的字符串)字典码,转化成值。 如:D0801–&amp;gt; 虚假电话。 转化后的结果是 : 2、解决...
  • oracle distinct 多列

    千次阅读 2013-11-21 18:56:39
    distinct这个关键字来过滤掉多余的重复...其原因是distinct只有用二重循环查询来解决,而这样对于个数据量非常大的站来说,无疑是会直接影响到效率的。 下面先来看看例子: table表 字段1 字段2 id name 1 a 2 ...
  • 【转】oracle行转列通用过程

    千次阅读 2009-12-22 18:26:00
    oracle行转列通用过程(转) 环境oracle 10g工作关系,常做些行转列报表,报表通常不是在大数据集合上处理.所以写了个过程.本过程比较适合在于需要动态输出报表的地方,例如web中.不是很完美,但已经可以解决绝大部分的...
  • --去重查询方法:根据id select * from sxe where id in(select min(id) from sxe group by username) order by id asc; --去重查询方法二:根据rownum select * from (select s.*,rownum rn from sxe s ) x ...
  • MySQL 面试题

    万次阅读 多人点赞 2019-09-02 16:03:33
    MySQL 涉及的内容非常非常非常,所以面试题也容易写的杂乱。当年,我们记着几个一定要掌握的重心: 重点的题目添加了【重点】前缀。 索引。 锁。 事务和隔离级别。 因为 MySQL 还会有部分内容和运维相关度比较...
  • 关于ORACLE中使用LIKE进行字段模糊匹配的解决思路
  • 面试 SQL整理 常见的SQL面试题:经典50题

    万次阅读 多人点赞 2019-09-12 22:16:11
    4.表查询 【面试题类型总结】这类题目属于行列如何互换,解题思路如下: 其他面试题: SQL基础知识整理: select 查询结果 如:[学号,平均成绩:组函数avg(成绩)]from 从哪张表中查找数...
  • 软件测试面试题(面试前准备篇)

    万次阅读 多人点赞 2019-09-27 10:42:37
    、问题预测 让简单介绍下自己(每次面试开场) 让说下自己会的内容 看了哪些书籍(有问到) 了解过哪些技术博客/论坛(有问到) 是否了解软件测试需要掌握哪些知识(问到类似问题) 之前面试过,觉得...
  • 数据库面试

    千次阅读 多人点赞 2019-02-13 09:03:42
    、数据库问答题 1. SQL语言包括哪些类型? 数据定义DDL:Create Table,Alter Table,Drop Table, Create/Drop Index等 数据操纵DML:Select ,insert,update,delete, 数据控制DCL:grant,revoke 2. 内联接,外联接...
  • 说明: 工作中经常遇到组or条数据按照不同类型被分成条数据,如 条合同的分期还款账单按照不同类型被分成本金,利息,管理费等, 数据统计工作中,条合同期就会分成条合同 如 实际工作中 常...
  • oracle 聚合函数 LISTAGG ,将多行结果合并成一行

    万次阅读 多人点赞 2017-05-22 10:29:48
    LISTAGG(列名,' 分割符号') oracle 11g 以上的版本才有的个将指定列名...需求:将 mb1_Transport_License_list 表中的数据,根据 transportation_license_id 数据进行分组, Item_Category_Name 的数据进行 去重
  • 测试开发笔记

    万次阅读 多人点赞 2019-11-14 17:11:58
    章 测试基础 7 什么是软件测试: 7 ★软件测试的目的、意义:(怎么做好软件测试) 7 3.软件生命周期: 7 第二章 测试过程 8 1.测试模型 8 H模型: 8 V模型 9 2.内部测试 10 3外部测试: 10 验收测试:(在系统...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 98,315
精华内容 39,326
关键字:

oracle行转列解决一对多