精华内容
下载资源
问答
  • Oracle行转列详解

    万次阅读 2019-07-05 17:16:00
    一、建表与插入数据 1.1、建表 create table kecheng (  id NUMBER,  name VARCHAR2(20),  course VARCHAR2(20), ...自: https://blog.csdn.net/w892824196/article/details/82222193

    一、建表与插入数据

    1.1、建表

    create table kecheng
    (
      id     NUMBER,
      name   VARCHAR2(20),
      course VARCHAR2(20),
      score  NUMBER
    );
    insert into kecheng (id, name, course, score)
    values (1, '张三', '语文', 67);
    insert into kecheng (id, name, course, score)
    values (1, '张三', '数学', 76);
    insert into kecheng (id, name, course, score)
    values (1, '张三', '英语', 43);
    insert into kecheng (id, name, course, score)
    values (1, '张三', '历史', 56);
    insert into kecheng (id, name, course, score)
    values (1, '张三', '化学', 11);
    insert into kecheng (id, name, course, score)
    values (2, '李四', '语文', 54);
    insert into kecheng (id, name, course, score)
    values (2, '李四', '数学', 81);
    insert into kecheng (id, name, course, score)
    values (2, '李四', '英语', 64);
    insert into kecheng (id, name, course, score)
    values (2, '李四', '历史', 93);
    insert into kecheng (id, name, course, score)
    values (2, '李四', '化学', 27);
    insert into kecheng (id, name, course, score)
    values (3, '王五', '语文', 24);
    insert into kecheng (id, name, course, score)
    values (3, '王五', '数学', 25);
    insert into kecheng (id, name, course, score)
    values (3, '王五', '英语', 8);
    insert into kecheng (id, name, course, score)
    values (3, '王五', '历史', 45);
    insert into kecheng (id, name, course, score)
    values (3, '王五', '化学', 1);
    commit;

    二、固定行列转换

    2.1、Decode方式

    SELECT ID,NAME,
    SUM(DECODE(course,'语文',score,0)) 语文,--这里使用max,min都可以
    SUM(DECODE(course,'数学',score,0)) 数学,
    SUM(DECODE(course,'英语',score,0)) 英语,
    SUM(DECODE(course,'历史',score,0)) 历史,
    SUM(DECODE(course,'化学',score,0)) 化学
    FROM kecheng
    GROUP BY ID ,NAME

    2.2、Case方式

    SELECT ID,NAME,
    MAX(CASE WHEN course='语文' THEN score ELSE 0 END) 语文,
    MAX(CASE WHEN course='数学' THEN score ELSE 0 END) 数学,
    MAX(CASE WHEN course='英语' THEN score ELSE 0 END) 英语,
    MAX(CASE WHEN course='历史' THEN score ELSE 0 END) 历史,
    MAX(CASE WHEN course='化学' THEN score ELSE 0 END) 化学
    FROM kecheng
    GROUP BY ID ,NAME

    2.3、wmsys.wm_concat行列转换函数

    SELECT ID,NAME,
    wmsys.wm_concat(course || ':'||score) course
    FROM kecheng
    GROUP BY ID ,NAME;

    2.4、使用over(partition by t.u_id)用法

    SELECT NAME, wmsys.wm_concat(course ||score) OVER (PARTITION BY NAME) FROM kecheng

    三、动态转换

    3.1、使用PL/SQL

    CREATE OR REPLACE PROCEDURE P_TOYGSEND IS
      --存放最终的SQL
      LV_SQL VARCHAR2(3000);
      --存放连接的SQL
      SQL_COMMOND VARCHAR2(3000);
      --定义游标
      CURSOR CUR IS
        SELECT COURSE FROM KECHENG GROUP BY COURSE;
    BEGIN
      --定义查询开头
      SQL_COMMOND := 'SELECT NAME ';

      FOR I IN CUR 
      LOOP
        --将结果相连接
        SQL_COMMOND := SQL_COMMOND || ' ,SUM(DECODE(course,''' || I.COURSE ||
                       ''',score,0)) ' || I.COURSE;
      END LOOP;

      SQL_COMMOND := SQL_COMMOND || ' from KECHENG group by name';

      LV_SQL := 'INSERT INTO temp_ss  ' || SQL_COMMOND;

      EXECUTE IMMEDIATE LV_SQL;
      commit;
    EXCEPTION
      WHEN OTHERS THEN
        ROLLBACK;
        NULL;
      
    END P_TOYGSEND;

    转自:https://blog.csdn.net/w892824196/article/details/82222193

    展开全文
  • 转字符串拼接 select id||username str from dual 字符串拆分方式: substr、instr、...行转列 wm_concat函数 select wm_concat(name) name from dual; SELECT to_char(wm_concat(decode(I.NAME, null...

    多列转字符串拼接

    select  id||username  str  from dual
    字符串拆分方式: substr、instr、regexp_substr函数方式 

    行转列 wm_concat函数

        select wm_concat(name) name from dual;
    
         SELECT to_char(wm_concat(decode(I.NAME, null, '', I.NAME || ': ') ||
                             decode(I2.NAME, null, '', I2.NAME || ': ') ||
                             decode(V.VALUE, null, '', 'Y', '√', V.VALUE))) favarite
      FROM CUST_BASE_INFO C

    行转列 ,默认逗号隔开
    这里写图片描述

    Oracle 11g 行列互换 pivot 和 unpivot 说明
    在Oracle 11g中,Oracle 又增加了2个查询:pivot(行转列) 和unpivot(列转行)
    pivot 列转行
    pivot(聚合函数 for 列名 in(类型)) ,其中 in('') 中可以指定别名,in中还可以指定子查询

    unpivot 行转列
    与pivot类似; unpivot没有聚合函数,执行速度效率也没有前者高

    展开全文
  • 文章目录pivot:行转列unpivot:转行 pivot:行转列 测试语句: WITH t_test AS( SELECT '1' ID, '张三' NAME, '70' score, 'CHINESE' subject FROM dual UNION ALL SELECT '1' ID, '张三' NAME, '90' score, '...

    pivot:行转列

    测试语句:

    WITH t_test AS(
      SELECT '1' ID, '张三' NAME, '70' score, 'CHINESE' subject FROM dual UNION ALL
      SELECT '1' ID, '张三' NAME, '90' score, 'MATH'    subject FROM dual UNION ALL
      SELECT '1' ID, '张三' NAME, '95' score, 'ENGLISH' subject FROM dual UNION ALL
      SELECT '2' ID, '李四' NAME, '75' score, 'CHINESE' subject FROM dual UNION ALL
      SELECT '2' ID, '李四' NAME, '85' score, 'MATH'    subject FROM dual UNION ALL
      SELECT '2' ID, '李四' NAME, '90' score, 'ENGLISH' subject FROM dual UNION ALL
      SELECT '3' ID, '王五' NAME, '90' score, 'CHINESE' subject FROM dual UNION ALL
      SELECT '3' ID, '王五' NAME, '90' score, 'MATH'    subject FROM dual UNION ALL
      SELECT '3' ID, '王五' NAME, '90' score, 'ENGLISH' subject FROM dual
    )
    SELECT *
      FROM t_test -- 请注意: ! 表别名无效 !
    pivot (SUM(score) -- 原有列值,必须是 聚合函数
           FOR subject IN('CHINESE' AS 语文, 'MATH' AS 数学, 'ENGLISH' AS 英语)) -- 原有列名,必须是已知的个数,如此处就是 3 个。
     -- WHERE id IN ('1', '2', '3')
     ORDER BY id;
    

    测试结果:(细节:列的个数必须是确定的,如:语文、数学、英语)
    在这里插入图片描述

    unpivot:列转行

    测试语句:

    WITH t_test AS (
      SELECT '1' ID, '张三' NAME, 70 chinese , 90 math , 95 english FROM dual UNION ALL
      SELECT '2' ID, '李四' NAME, 75 chinese , 85 math , 90 english FROM dual UNION ALL
      SELECT '3' ID, '王五' NAME, 90 chinese , 90 math , 90 english FROM dual
    )
    SELECT ID,
           NAME,
           score 成绩,
           subject 学科
      FROM t_test -- 请注意: ! 表别名无效 !
      unpivot(score FOR subject IN(chinese, math, english)) -- 新增列: score、subject
     -- WHERE ID IN ('1', '2', '3')
     ORDER BY ID;
    

    测试结果:
    在这里插入图片描述

    展开全文
  • oracle数据库 行转列 转行详解

    千次阅读 2015-03-10 15:06:06
    [一]、行转列   1.1、初始测试数据   表结构:TEST_TB_GRADE   create table TEST_TB_GRADE (  ID NUMBER(10) not null,  USER_NAME VARCHAR2(20 CHAR),  COURSE...

    [一]、行转列

     

    1.1、初始测试数据

     

    表结构:TEST_TB_GRADE

     

    create  table TEST_TB_GRADE   
    1. (   
    2.   ID        NUMBER(10) not null,   
    3.   USER_NAME VARCHAR2(20 CHAR),   
    4.   COURSE    VARCHAR2(20 CHAR),   
    5.   SCORE     FLOAT  
    6. )  
    [sql]  view plain copy
    1. create table TEST_TB_GRADE  
    2. (  
    3.   ID        NUMBER(10) not null,  
    4.   USER_NAME VARCHAR2(20 CHAR),  
    5.   COURSE    VARCHAR2(20 CHAR),  
    6.   SCORE     FLOAT  
    7. )  

     初始数据如下图:


                            

     

    1.2、 如果需要实现如下的查询效果图:


                         

     

    这就是最常见的行转列,主要原理是利用decode函数、聚集函数(sum),结合group by分组实现的,具体的sql如下:

    select t.user_name,   
    1.   sum(decode(t.course, '语文', score,null)) as CHINESE,   
    2.   sum(decode(t.course, '数学', score,null)) as MATH,   
    3.   sum(decode(t.course, '英语', score,null)) as ENGLISH   
    4. from test_tb_grade t   
    5. group by t.user_name   
    6. order by t.user_name  
    [sql]  view plain copy
    1. select t.user_name,  
    2.   sum(decode(t.course, '语文', score,null)) as CHINESE,  
    3.   sum(decode(t.course, '数学', score,null)) as MATH,  
    4.   sum(decode(t.course, '英语', score,null)) as ENGLISH  
    5. from test_tb_grade t  
    6. group by t.user_name  
    7. order by t.user_name  
     

     

    1.3、延伸

     

    如果要实现对各门功课的不同分数段进行统计,效果图如下:


                     

     

    具体的实现sql如下:

    select t2.SCORE_GP,   
    1.   sum(decode(t2.course, '语文', COUNTNUM,null)) as CHINESE,   
    2.   sum(decode(t2.course, '数学', COUNTNUM,null)) as MATH,   
    3.   sum(decode(t2.course, '英语', COUNTNUM,null)) as ENGLISH   
    4. from (   
    5.   select t.course,   
    6.          case when t.score  <60 then '00-60'  
    7.               when t.score >=60 and t.score <80  then '60-80'  
    8.               when t.score >=80 then '80-100' end as SCORE_GP,   
    9.          count(t.score) as COUNTNUM   
    10.   FROM test_tb_grade t   
    11.   group by t.course,    
    12.         case when t.score  <60  then '00-60'  
    13.               when t.score >=60 and t.score <80  then '60-80'  
    14.               when t.score >=80 then '80-100' end  
    15.   order by t.course ) t2   
    16. group by t2.SCORE_GP   
    17. order by t2.SCORE_GP  
    [sql]  view plain copy
    1. select t2.SCORE_GP,  
    2.   sum(decode(t2.course, '语文', COUNTNUM,null)) as CHINESE,  
    3.   sum(decode(t2.course, '数学', COUNTNUM,null)) as MATH,  
    4.   sum(decode(t2.course, '英语', COUNTNUM,null)) as ENGLISH  
    5. from (  
    6.   select t.course,  
    7.          case when t.score  <60 then '00-60'  
    8.               when t.score >=60 and t.score <80  then '60-80'  
    9.               when t.score >=80 then '80-100' end as SCORE_GP,  
    10.          count(t.score) as COUNTNUM  
    11.   FROM test_tb_grade t  
    12.   group by t.course,   
    13.         case when t.score  <60  then '00-60'  
    14.               when t.score >=60 and t.score <80  then '60-80'  
    15.               when t.score >=80 then '80-100' end  
    16.   order by t.course ) t2  
    17. group by t2.SCORE_GP  
    18. order by t2.SCORE_GP  
     

    [二]、列转行

     

    1.1、初始测试数据

            表结构:TEST_TB_GRADE2

    create  table TEST_TB_GRADE2   
    1. (   
    2.   ID         NUMBER(10) not null,   
    3.   USER_NAME  VARCHAR2(20 CHAR),   
    4.   CN_SCORE   FLOAT,   
    5.   MATH_SCORE FLOAT,   
    6.   EN_SCORE   FLOAT  
    7. )  
    [sql]  view plain copy
    1. create table TEST_TB_GRADE2  
    2. (  
    3.   ID         NUMBER(10) not null,  
    4.   USER_NAME  VARCHAR2(20 CHAR),  
    5.   CN_SCORE   FLOAT,  
    6.   MATH_SCORE FLOAT,  
    7.   EN_SCORE   FLOAT  
    8. )  
     

            初始数据如下图:


            

     

    1.2、 如果需要实现如下的查询效果图:


                           

     

    这就是最常见的列转行,主要原理是利用SQL里面的union,具体的sql语句如下:

    select user_name,  '语文' COURSE , CN_SCORE  as SCORE  from test_tb_grade2    
    1. union select user_name, '数学' COURSE, MATH_SCORE as SCORE from test_tb_grade2    
    2. union select user_name, '英语' COURSE, EN_SCORE as SCORE from test_tb_grade2    
    3. order by user_name,COURSE   
    [sql]  view plain copy
    1. select user_name, '语文' COURSE , CN_SCORE as SCORE from test_tb_grade2   
    2. union select user_name, '数学' COURSE, MATH_SCORE as SCORE from test_tb_grade2   
    3. union select user_name, '英语' COURSE, EN_SCORE as SCORE from test_tb_grade2   
    4. order by user_name,COURSE   

     

     也可以利用【 insert all into ... select 】来实现,首先需要先建一个表TEST_TB_GRADE3:

    create  table TEST_TB_GRADE3     
    1.     (    
    2.       USER_NAME VARCHAR2(20 CHAR),     
    3.       COURSE    VARCHAR2(20 CHAR),     
    4.       SCORE     FLOAT     
    5.     )    
    [sql]  view plain copy
    1. create table TEST_TB_GRADE3    
    2.     (   
    3.       USER_NAME VARCHAR2(20 CHAR),    
    4.       COURSE    VARCHAR2(20 CHAR),    
    5.       SCORE     FLOAT    
    6.     )    

     再执行下面的sql:

     

    insert  all  
    1. into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, '语文', CN_SCORE)   
    2. into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, '数学', MATH_SCORE)   
    3. into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, '英语', EN_SCORE)   
    4. select user_name, CN_SCORE, MATH_SCORE, EN_SCORE from test_tb_grade2;   
    5. commit;  
    [sql]  view plain copy
    1. insert all  
    2. into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, '语文', CN_SCORE)  
    3. into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, '数学', MATH_SCORE)  
    4. into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, '英语', EN_SCORE)  
    5. select user_name, CN_SCORE, MATH_SCORE, EN_SCORE from test_tb_grade2;  
    6. commit;  

     别忘记commit操作,然后再查询TEST_TB_GRADE3,发现表中的数据就是列转成行了。

    展开全文
  • 一 pivot函数表达式: 有一个职工部门工资表pivot_tmp,数据如下: 如果我们想要求各部门的工资和,很简单一个sum和group by 就满足了。 select t.dept_name 部门,sum(t.salary) 工资总额 from pivot_tmp t ...
  • Oracle行转列转行的Sql语句总结

    万次阅读 2015-06-12 17:11:09
    行转列 转行 [一]、行转列   1.1、初始测试数据   表结构:TEST_TB_GRADE   Sql代码  create table TEST_TB_GRADE  (   ID NUMBER(10) not null,   USER_NAME VARCHAR2(20 CHAR),   ...
  • oracle行转列函数pivot

    千次阅读 2019-04-16 19:25:43
    什么情形下需要使用行转列 对于同一类的事物下具有多个属性(属性是有限的),比如说学生选课,每一名可以选择多个课程,而且课程的数量也是有限重复的(对于不通学生选同一门科就相当于是课程重复)。比如说下面这样的...
  • Oracle Rownum 伪列详解

    千次阅读 2009-12-09 11:33:00
    Rowid 与 Rownum 虽都被称为伪,但它们的存在方式是不一样的,rowid 可以说是物理存在的,表示记录在表空间中的唯一位置ID,在DB中唯一。只要记录没被搬动过,rowid是不变的。rowid 相对于表来说又像表中的一般...
  • Oracle INSTR函数(子字符串查询)语法详解及应用实例 http://blog.csdn.net/hpdlzu80100/article/details/53890136 WM_CONCAT()函数在Oracle Database SQL Reference 中没找到,发现以下一篇博文不错,特此...
  • oracle 转行 listagg()函数 详解

    千次阅读 2018-10-24 10:29:10
    listagg() : Oracle转行函数; 版本要求: 11.2 以上版本. 语法:  listagg(iw.wfl_id,',') within group(order by ir.serno) listagg(列名,' 分割符号') within group(order by 值被拼接的顺序) 分组...
  • HH 终风且暴,顾我则笑,谑浪笑敖,中心是悼。 终风且霾,惠然肯来,莫往莫来,悠悠我思。 博客园 首页 ...Oracle行转列转行的Sql语句总结 多行转字符串 这个比较简单,用||或concat函数...
  • ORACLE触发器详解

    万次阅读 多人点赞 2012-09-27 10:00:56
     ORACLE可以在DML语句进行触发,可以在DML操作前或操作后进行触发,并且可以对每个或语句操作上进行触发。   8.1.2 替代触发器  由于在ORACLE里,不能直接对由两个以上的表建立的视图进行操作。所以给...
  • oracle行转列函数

    2017-04-27 14:58:53
    用这两个函数的时候发现一个问题,就是如果被转换的的类型是 NVARCHAR2 ,返回的结果就是空。 这个问题还没有解决,怀疑是字符集的问题,但是改为utf-8之后,还是有此情况。 有大神知晓其中原因的话,请...
  • Oracle中的伪列详解

    2014-03-10 10:06:37
    就像Oracle中的一个表,但实际上它并未存储在表中,记录的是该行的物理地址。伪可以从表中查询,但是不能插入、更新或删除它们的值。常用的伪有:rowid和rownum。 Rowid:数据库中的每一都有一个地址...
  • 工作中用到一段比较复杂的...说简单点,listagg()函数可以实现多记录聚合为一条记录,从而实现数据的压缩、致密化(data densification)。以下内容转载自http://dacoolbaby.iteye.com/blog/1698957,SQL脚本做了...
  • 1. Oracle 的类型转换分为 '自动类型转换(隐式)' 和 '强制类型转换(显式)' 2. 应尽量避免 '隐式' 转换,尤其在 '索引' 上 2 显式转换 2.1 to_char 功能:将 '数值类型' 或 '日期类型' 转为 '字符类型' ...
  • 上面的表格是一张竖表,而我们想得到的是横表,即行转列,获得如下结果 编号 时间 水耗 电耗 热耗 SH 2018-08-29 01:00:00 22.95 18.4556 33.254 GZ 2018-...
  • Oracle中imp命令详解

    万次阅读 2019-09-14 17:20:10
    oracle中imp命令详解 Oracle的导入实用程序(Import utility)允许从数据库提取数据,并且将数据写入操作系统文 件。imp使用的基本格式:imp[username[/password[@service]]],以下例举imp常用用 法。 1. 获取帮助 imp...
  • ORACLE用多列转多行

    千次阅读 2019-08-01 13:30:51
    ORACLE用多列转多行@[ORACLE用unpovit多列转多行] ORACLE用多列转多行 将一个对象(name1)多个属性行列转换 原始数据 执行语句 select distinct a.name, a.type, a.t, b.c from ( select row_number() over...
  • 主要介绍了Oracle sqlldr导入一个日期实例详解的相关资料,需要的朋友可以参考下
  • ORACLE数据泵使用详解

    千次阅读 2019-03-28 15:14:32
    ORACLE数据泵使用详解 来源于:http://blog.sina.com.cn/s/blog_490a0c990100wh4y.html http://blog.csdn.net/jojo52013145/article/details/7966047 数据泵使用EXPDP和IMPDP时应该注意的事项: EXP和IMP是...
  • oracleOracle LOB 详解

    千次阅读 2017-10-12 16:50:03
    Oracle 11gR2 文档: LOB Storage http://download.oracle.com/docs/cd/E11882_01/appdev.112/e18294/adlob_tables.htm#ADLOB45267   Oracle 10gR2 文档: LOBs in Tables http://download.
  • Oracle Check约束用法详解

    千次阅读 2019-04-16 15:38:21
    Oracle Check约束用法详解 目标 实例讲解在Oracle中如何使用CHECK约束(创建、启用、禁用和删除) 什么是Check约束? CHECK约束指在表的中增加额外的限制条件。 注: CHECK约束不能在VIEW中定义。 CHECK约束只能...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 21,147
精华内容 8,458
关键字:

oracle行转列详解