精华内容
下载资源
问答
  • 3.转多列并包含多个名称 select * from t_demo_unpivot unpivot((total,min) for name in ((APPLE_TOTAL,APPLE_MIN) AS '苹果', (ORANGE_TOTAL,ORANGE_MIN) AS '橘子', (GRAPE_TOTAL,GRAPE_MIN) AS '葡萄',...

    Pivot

    1、准备数据

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

    2、Pivot行转多列

    select * 
    from (select name, nums from t_demo) 
    pivot (sum(nums) total,min(nums) min for name in ('苹果' apple, '橘子' orange, '葡萄' grape, '芒果' mango));

     

    Unpivot

    1、准备数据

    CREATE TABLE t_demo_unpivot as
    select * 
    from (select name, nums from t_demo) 
    pivot (sum(nums) total,min(nums) min for name in ('苹果' apple, '橘子' orange, '葡萄' grape, '芒果' mango));

    2.列转行

    select * from t_demo_unpivot unpivot(nums for name in (APPLE_TOTAL,APPLE_MIN,ORANGE_TOTAL,ORANGE_MIN,GRAPE_TOTAL,GRAPE_MIN,MANGO_TOTAL,MANGO_MIN))
    

    3.转多列并包含多个名称

    select * 
    from t_demo_unpivot 
    unpivot((total,min) for name in ((APPLE_TOTAL,APPLE_MIN) AS '苹果',
                                     (ORANGE_TOTAL,ORANGE_MIN) AS '橘子',
                                     (GRAPE_TOTAL,GRAPE_MIN) AS '葡萄',
                                     (MANGO_TOTAL,MANGO_MIN) AS '芒果'
                                    ) 
            ) 

    展开全文
  • CREATE TABLE T5 ( idnum NUMBER ...UnPivot ( ( F , E , H ) for sk in ( ( F1 , E1 , H1 ) as 1 , ( F2 , E2 , H2 ) as 2 , ( F3 , E3 , H3 ) as 3 ) ) order by IDNUM , SK ;
    CREATE TABLE  T5 (idnum NUMBER,f1 NUMBER(10,5),f2 NUMBER(10,5),f3 NUMBER(10,5),e1 NUMBER(10,5),e2 NUMBER(10,5),h1 NUMBER(10,5),h2 NUMBER(10,5));
     
    INSERT INTO T5 (IDNUM,F1,F2,F3,E1,E2,H1,H2) VALUES (1,'10.2004','5.009','7.330','9.008','8.003','.99383','1.43243');
    INSERT INTO T5 (IDNUM,F1,F2,F3,E1,E2,H1,H2) VALUES (2,'4.2004','6.009','9.330','4.7008','4.60333','1.993','3.3243');
    INSERT INTO T5 (IDNUM,F1,F2,F3,E1,E2,H1,H2) VALUES (3,'10.2040','52.6009','67.330','9.5008','8.003','.99383','1.43243');
    INSERT INTO T5 (IDNUM,F1,F2,F3,E1,E2,H1,H2) VALUES (4,'9.20704','45.009','17.330','29.008','5.003','3.9583','1.243');
     
    COMMIT;
     
    select *
    from (select IDNUM,F1,F2,F3,E1,E2,H1,H2, null as E3,null as H3
            from T5)
    UnPivot((F,E,H) for sk in ((F1,E1,H1) as 1, (F2,E2,H2) as 2, (F3,E3,H3) as 3))
    order by IDNUM,SK;
    
    展开全文
  • 多字段unpivot

    千次阅读 2014-02-19 15:59:51
    http://stackoverflow.com/questions/18617243/sql-unpivot-multiple-columns-data
    http://stackoverflow.com/questions/18617243/sql-unpivot-multiple-columns-data
    展开全文
  • 这是一复杂的例子,想对它有一深入的使用,那仔细看看吧 点击(此处)折叠或打开 create table TT_TEST2 ...
    这是一个复杂的例子,想对它有一个深入的使用,那仔细看看吧

    点击(此处)折叠或打开

    1. create table TT_TEST2
    2. (
    3.   allup_level NUMBER(2),
    4.   cross_flag NUMBER(1),
    5.   book_id NUMBER(10),
    6.   bit_state VARCHAR2(10),
    7.   pool1_id NUMBER(10),
    8.   selection1_no VARCHAR2(10),
    9.   goal_line1 NUMBER(5,2),
    10.   pool2_id NUMBER(10),
    11.   selection2_no VARCHAR2(10),
    12.   goal_line2 NUMBER(5,2),
    13.   pool3_id NUMBER(10),
    14.   selection3_no VARCHAR2(10),
    15.   goal_line3 NUMBER(5,2),
    16.   pool4_id NUMBER(10),
    17.   selection4_no VARCHAR2(10),
    18.   goal_line4 NUMBER(5,2),
    19.   pool5_id NUMBER(10),
    20.   selection5_no VARCHAR2(10),
    21.   goal_line5 NUMBER(5,2),
    22.   pool6_id NUMBER(10),
    23.   selection6_no VARCHAR2(10),
    24.   goal_line6 NUMBER(5,2),
    25.   pool7_id NUMBER(10),
    26.   selection7_no VARCHAR2(10),
    27.   goal_line7 NUMBER(5,2),
    28.   pool8_id NUMBER(10),
    29.   selection8_no VARCHAR2(10),
    30.   goal_line8 NUMBER(5,2),
    31.   ticket_count NUMBER(10),
    32.   investment NUMBER(19),
    33.   liability NUMBER(22,2),
    34.   act_liability NUMBER(22,2)
    35. );
    36. insert into TT_TEST2 (allup_level, cross_flag, book_id, bit_state, pool1_id, selection1_no, goal_line1, pool2_id, selection2_no, goal_line2, pool3_id, selection3_no, goal_line3, pool4_id, selection4_no, goal_line4, pool5_id, selection5_no, goal_line5, pool6_id, selection6_no, goal_line6, pool7_id, selection7_no, goal_line7, pool8_id, selection8_no, goal_line8, ticket_count, investment, liability, act_liability)
    37. values (3, 0, 0, \'0\', 2293079, \'+5\', null, 2293087, \'+3\', null, 2293127, \'-2\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 1, 198, 1767.74, 0);
    38. insert into TT_TEST2 (allup_level, cross_flag, book_id, bit_state, pool1_id, selection1_no, goal_line1, pool2_id, selection2_no, goal_line2, pool3_id, selection3_no, goal_line3, pool4_id, selection4_no, goal_line4, pool5_id, selection5_no, goal_line5, pool6_id, selection6_no, goal_line6, pool7_id, selection7_no, goal_line7, pool8_id, selection8_no, goal_line8, ticket_count, investment, liability, act_liability)
    39. values (2, 0, 0, \'0\', 2293079, \'+5\', null, 2293087, \'+3\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 1, 198, 883.87, 0);
    40. insert into TT_TEST2 (allup_level, cross_flag, book_id, bit_state, pool1_id, selection1_no, goal_line1, pool2_id, selection2_no, goal_line2, pool3_id, selection3_no, goal_line3, pool4_id, selection4_no, goal_line4, pool5_id, selection5_no, goal_line5, pool6_id, selection6_no, goal_line6, pool7_id, selection7_no, goal_line7, pool8_id, selection8_no, goal_line8, ticket_count, investment, liability, act_liability)
    41. values (2, 0, 0, \'0\', 2293087, \'+3\', null, 2293127, \'-2\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 1, 198, 792, 0);
    42. insert into TT_TEST2 (allup_level, cross_flag, book_id, bit_state, pool1_id, selection1_no, goal_line1, pool2_id, selection2_no, goal_line2, pool3_id, selection3_no, goal_line3, pool4_id, selection4_no, goal_line4, pool5_id, selection5_no, goal_line5, pool6_id, selection6_no, goal_line6, pool7_id, selection7_no, goal_line7, pool8_id, selection8_no, goal_line8, ticket_count, investment, liability, act_liability)
    43. values (2, 0, 0, \'0\', 2293079, \'+5\', null, 2293127, \'-2\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 1, 198, 883.87, 0);
    44. commit;
    以上是基础数据,有点长,
    以下是sql

    点击(此处)折叠或打开

    1. WITH a AS
    2.        (SELECT allup_level,
    3.                cross_flag,
    4.                pool_id,
    5.                selection_no,
    6.                ticket_count,
    7.                investment
    8.         FROM TT_TEST2 p unpivot

    9. ((pool_id, selection_no) FOR(x1, x2) IN((pool1_id,
    10.                                                                                 selection1_no),
    11.                                                                                (pool2_id,
    12.                                                                                 selection2_no),
    13.                                                                                (pool3_id,
    14.                                                                                 selection3_no),
    15.                                                                                (pool4_id,
    16.                                                                                 selection4_no),
    17.                                                                                (pool5_id,
    18.                                                                                 selection5_no),
    19.                                                                                (pool6_id,
    20.                                                                                 selection6_no),
    21.                                                                                (pool7_id,
    22.                                                                                 selection7_no),
    23.                                                                                (pool8_id,
    24.                                                                                 selection8_no)))
    25.         WHERE pool_id > 0)
    26.       SELECT *
    27.       FROM a pivot ( SUM(decode(cross_flag, 1, ticket_count, 0)) AS tx, SUM(decode(cross_flag, 1, 0, ticket_count)) AS t, SUM(decode(cross_flag, 1, investment, 0)) AS ix, SUM(decode(cross_flag, 1, 0, investment)) AS i FOR allup_level IN(2 AS v2,
    28.                                                                                                                                                                                                                                              3 AS v3,
    29.                                                                                                                                                                                                                                              4 AS v4,
    30.                                                                                                                                                                                                                                              5 AS v5,
    31.                                                                                                                                                                                                                                              6 AS v6,
    32.                                                                                                                                                                                                                                              7 AS v7,
    33.                                                                                                                                                                                                                                              8 AS v8))
    不解释,自己感受一下

    来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/134308/viewspace-1175926/,如需转载,请注明出处,否则将追究法律责任。

    转载于:http://blog.itpub.net/134308/viewspace-1175926/

    展开全文
  • Oracle利用PIVOT和UNPIVOT进行行列转换

    千次阅读 2017-08-17 14:24:16
    在数据库开发中,我们会经常遇到行转列和列转行的情况,方法很,比如使用CASE...WHEN...、DECODE,或者PIVOT和UNPIVOT都可以实现该功能,本篇讲解下PIVOT和UNPIVOT的使用,希望可以让读者举一反三,了解行列转换的...
  • 转换多个列 select * from (select month,prd_type_id,amount from all_sales ) pivot(sum(amount) for (month,prd_type_id) in ( (1,2) as JAN_P2,(2,3) as FEB_P3) ); 在转换中使用多个聚合函数 select * from ...
  • 今天写sql语句,需要取出多个字段列中的最大值和最小值。  本来想到的做法比较麻烦,要分别取出max(one),max(two),max(three),放到pb中在编程处理。  后来找到个greatest 函数和least函数,只用写greatest (max...
  • oracle 行转列(单字段) --CHBGID,bw1为数据库原字段;beiyong, cdbh 为临时字段;in()里面的为需要转列的字段 select CHBGID,bw1 , beiyong, cdbh from BG_CHJL unpivot (cdbh for beiyong in (cdbh1,cdbh2,cdbh3...
  • 最近一WEBSERVICE突然变慢了,后查询发现,后台查询也非常慢(记录条数800万),索引也有,如下语句 SELECT P.ID,P.RECORD_ID,P.KEY_NAME,P.KEY_CONTENT   FROM MED_EMR_PATIENT_RECORD R,MED_EMR_...
  • PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT 与 PIVOT 执行相反的操作,将表值表达式的列转换为列值。 注意 ...
  • oracle11g开始内置了数据透视表pivot table这一功能,可以用来实现行列转换的功能,但是在数据量较大的时候使用性能就会较差。 pivot语法为: SELECT ... FROM ... PIVOT [XML] (pivot_clause pivot_for_...
  • 今天写sql 语句,需要取出多个字段列中的最大值和最小值。 本来想到的做法比较麻烦,要分别取出max(one),max(two),max(three),放到pb中在编程处理。 后来同事夏老帮忙找到个greatest 函数和least函数,只用写...
  • 8、知道列的命名规则,可以在Select字段中列出要查询的列; select d30_mgr_sum_sal, d30_mgr_cnt  from (select job ,deptno, sal from emp)  pivot(  sum(sal) as sum_sal,count(1) as cnt  for (deptno,...
  • SELECT  ORDERID,  NAME,  ID,  WORK_TYPE,  WORK_TIME FROM  HR_ATTENDANCE_MANAGEMENT_F UNPIVOT (  WORK_TYPE FOR WORK_TIME IN (  TWENTYSEVEN,  TWENTYEITHT, ...
  • 针对Oracle 11g 之前版本的行列转换,之前整理过一篇文档:Oracle 行列转换 总结http://blog.csdn.net/tianlesoftware/article/details/4704858 在Oracle 11g中,Oracle 又增加了2查询:pivot 和 unpivot。...
  • Oracle PIVOT和UNPIVOT

    2020-09-17 22:58:32
    您是否曾经需要将一组数据从行转换为列?您可以使用Oracle PIVOT功能(相反,使用Oracle ... 执行多个聚合 按多列分组 XML的PIVOT示例 动态指定列 使用UNPIVOT将列转换为行 一个简单的UNPIVOT示例 在UNPIVOT
  • oracle pivot / unpivot

    2019-07-03 10:46:00
     1.2 示例:多个字段聚集 -- pivot 使用多个聚集 select * from ( select t.job, t.deptno, t.sal from emp t) pivot( sum (sal) sal, count (sal) cnt for deptno in ( 10 dept_10, 20 ...
  • Oracle SQL函数pivot、unpivot转置函数实现行转列、列转行
  • 语句,需要取出多个字段列中的最大值和最小值。  本来想到的做法比较麻烦,要分别取出max(one),max(two),max(three),放到pb中在编程处理。  后来同事夏老帮忙找到个greatest 函数和least函数,...
  • PostgreSQL , Oracle , 行列变换 , pivot , unpivot , tablefunc , crosstab , json , jsonb 背景 行列转换是OLAP应用场景中,对数据透视常用的SQL之一。 Oracle pivot 行转列 语法如下: SELECT ... FROM ... ...
  • Oracle 行转列、列转行 的Sql语句总结
  • Oracle 11g 行列互换 pivot 和 unpivot 说明https://blog.csdn.net/tianlesoftware/article/details/7060306版权声明: https://blog.csdn.net/tianlesoftware/article/details/7060306 针对Oracle 11g 之前版本...
  • 注意: unpivot没有聚合函数,xiaoshou、jidu字段也是临时的变量   同样不使用unpivot也可以实现同样的效果,只是sql语句会很长,而且执行速度效率也没有前者高   ? ...
  • 今天写sql 语句,需要取出多个字段列中的最大值和最小值。 本来想到的做法比较麻烦,要分别取出max(one),max(two),max(three),放到pb中在编程处理。 后来同事夏老帮忙找到个greatest 函数和least函数,只用写...
  • Oracle 11g中,Oracle 又增加了2查询:pivot(行转列) 和unpivot(列转行) pivot 列转行 测试数据 (id,类型名称,销售数量),案例:根据水果的类型查询出一条数据显示出每种类型的销售数量。 SQL Code ...
  • 1、pivot子句是Oracle database 11g的新增特性, 2、可以在查询输出中将行旋转为列,同时对数据使用聚合函数。 3、同时新增了unpivot子句,他可以在查询输出中将列旋转为行; 例如: 我们sql会写为1、 select prd_...
  • HH 终风且暴,顾我则笑,谑浪笑敖,中心是悼。 终风且霾,惠然肯来,莫往莫来,悠悠我思。 博客园 首页 新随笔 联系 ...Oracle行转列、列转行的Sql语句总结 ...这比较简单,用||或concat函数...
  •  cxcyxm02 :操作表,含有一个存储另一张表(xs0101)多个主键id的字段   ids :操作表的主键 id */ CREATE OR REPLACE FUNCTION cxcyxmcyry(ids VARCHAR2) RETURN VARCHAR2 IS tempcyryxx VARCHAR2(1

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 406
精华内容 162
热门标签
关键字:

oracleunpivot多个字段