精华内容
下载资源
问答
  • Oracle列转行unpivot函数的使用

    千次阅读 2020-11-29 18:51:32
    这里写自定义目录标题欢迎使用Markdown编辑器新的改变功能快捷键合理的创建标题,有助于目录的生成如何改变文本的...Oracle中列转行unpivot函数的使用 欢迎使用Markdown编辑器 你好! 这是你第一次使用 Markdown编辑

    行转列函数:unpivot
    说明:将表中多个列缩减为一个聚合列
    语法:unpivot(新列名 for 聚合列名 in (对应的列名1…列名n ))

    创建表
    create table score(
    姓名 varchar2(20),
    语文 number,
    数学 number,
    英语 number
    );

    插入数据
    insert into score values (‘张三’,99,88,78);
    insert into score values (‘李四’,93,82,75);
    insert into score values (‘王五’,95,83,76);
    原表数据
    使用unpivot函数行转列

    select 姓名,科目,成绩
    from score unpivot ( 成绩 for 科目 in ( 语文, 数学, 英语 ) );

    行转列结果

    展开全文
  • Mysql中行转列算法

    2012-12-24 14:28:20
    mysql行转列算法举例与分析,有益于对mysql语法学习
  • 分析函数==== 1.lag over取当前列的上一列数据信息,类似于lag over (类似行转列的形式实现) select max(if(rank=1,username,0)) ,max(if(rank=2,username,0)) from ( select id ,username,@rank:=@rank+1 as rank ...

    =====1.日期相关===
    //获取当前日期在本周的周一
    select subdate(now(),date_format(now(),'%w')-1);
    //获取当前日期在本周的周日
    select subdate(now(),date_format(now(),'%w')-7);
    //获取月的第一天
    select concat(date_format(now(), '%y-%m'),'-01');
    //字符串转日期
    select str_to_date('2008-4-2 15:3:28','%Y-%m-%d %H:%i:%s');
    //日期转字符串
    select date_format(now(),'%y-%m-%d');

    =====2.分析函数====
    1.lag over取当前列的上一列数据信息,类似于lag over (类似行转列的形式实现)
    select max(if(rank=1,username,0)) ,max(if(rank=2,username,0)) from (
    select id ,username,@rank:=@rank+1 as rank
    from users,(select @rank:=0) q
    order by username
    ) rs limit 1
    2.sum over

    3.rank over
    select id ,username,@rank:=@rank+1 as rank
    from users,(select @rank:=0) q
    order by username

    =====3.特殊函数====
    1.nvl
    IFNULL(expr1,expr2) ,如果expr1不是NULL,IFNULL()返回expr1,否则它返回expr2。IFNULL()返回一个数字或字符串值,取决于它被使用的上下文环境。

    2.rownum

    3.decode
    if(expr,value1,value2) , 如果表达式expr成立则返回value1,反之返回value2

    4.merge
    INSERT INTO ... ON DUPLICATE KEY UPDATE:
    INSERT INTO users(id,username) select * from (select 1 as id,'zs' as username) rs ON DUPLICATE KEY UPDATE username=rs.username
    REPLACE INTO:
    REPLACE INTO users(ID,USERNAME) select * from (select 1 as id,'zs' as username) rs
    注:replace into 是采用的先删除后插入的方式,没有被replace的值会填充为默认值.二者均需要表里面有唯一键或主键

     

    转载于:https://www.cnblogs.com/gavinYang/p/11197862.html

    展开全文
  • [size=large]只有Oracle 11G才支持的行列转置的函数[color=indigo][b] UNPIVOT[/b][/color][/size] 操作代码如下 [code="SQL"] drop table t; CREATE TABLE T (ID NUMBER,phonetype VARCHAR2(10...
    [size=large]只有Oracle 11G才支持的行列转置的函数[color=indigo][b] UNPIVOT[/b][/color][/size]
    


    操作代码如下


    drop table t;
    CREATE TABLE T (ID NUMBER,phonetype VARCHAR2(10),qhd VARCHAR2(10),ts varchar2(10),bd varchar2(10));
    INSERT INTO T VALUES ( 1,'htc', '02' ,'03','05' );
    INSERT INTO T VALUES ( 2,'moto', '05' ,'08','06' );
    INSERT INTO T VALUES ( 3,'se', 'oracle' ,'mysql','07');


    select * from
    (
    select id, col, val
    from t
    unpivot ( val for col in (qhd,ts,bd) )
    )



    PS 此代码来自 http://alafqq.iteye.com/blog/908336
    展开全文
  • https://www.cnblogs.com/mellowsmile/p/4642306.html HH 终风且暴,顾我则笑,谑浪笑敖,中心是悼。 终风且霾,惠然肯来,莫往莫来,悠悠我思。 博客园 首页 ...这个比较简单,用||或concat函数...

    https://www.cnblogs.com/mellowsmile/p/4642306.html

    HH

    终风且暴,顾我则笑,谑浪笑敖,中心是悼。 终风且霾,惠然肯来,莫往莫来,悠悠我思。

    随笔 - 48  文章 - 0  评论 - 12

    Oracle行转列、列转行的Sql语句总结

    多行转字符串

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

     SQL Code 

    1
    2

      

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

    字符串转多列

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

    字符串转多行

    使用union all函数等方式

    wm_concat函数

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

     SQL Code 

    1
    2
    3
    4
    5
    6

      

    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 : 行转列 ,默认逗号隔开

     SQL Code 

    1

      

    select wm_concat(name) name from test;

       

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

     SQL Code 

    1

      

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

       

    效果3: 按ID分组合并name

     SQL Code 

    1

      

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

       

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

      SQL Code 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36

      

    -------- 适用范围: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;

       

    懒人扩展用法:

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

     SQL Code 

    1
    2
    3
    4
    5

      

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

       

    利用系统表方式查询

     SQL Code 

    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,类型名称,销售数量),案例:根据水果的类型查询出一条数据显示出每种类型的销售数量。

     SQL Code 

    1
    2
    3
    4
    5
    6
    7
    8
    9

      

    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);

       

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

     SQL Code 

    1

      

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

    行转列查询

     SQL Code 

    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函数,等同于下列语句,只是代码比较长,容易理解

     SQL Code 

    1
    2
    3
    4
    5

      

    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个季度的销售数量,现在要将每种水果的每个季度的销售情况用多行数据展示。

    创建表和数据

     SQL Code 

    1
    2
    3
    4
    5
    6

      

    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

    列转行查询

     SQL Code 

    1

      

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

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

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

     SQL Code 

    1
    2
    3
    4
    5
    6
    7

      

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

    XML类型

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

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

    示例如下:

     SQL Code 

    1
    2
    3
    4
    5
    6
    7

      

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

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

    对于该xml文件的解析,贴代码如下:

      SQL Code 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51

     

    create or replace procedure ljz_pivot_xml_sp(pi_table_name   varchar2,
                                                 pi_column_name  varchar2,
                                                 pi_create_table varchar2) as
      v_column      nvarchar2(50);
      v_count       number := 0;
      v_i           number;
      v_parent_node nvarchar2(4000);
      v_child_node  nvarchar2(4000);
      v_over        boolean := false;
      v_tmp         nvarchar2(50);
      v_existsnode  number;
      v_sql         clob;
      v_name        varchar2(30);
      v_name_xml    xmltype;
    begin
      v_sql         := 'select x.* from ' || pi_table_name ||
                       ' a, xmltable(''/PivotSet'' passing a.' ||
                       pi_column_name || ' columns ';
      v_parent_node := '/PivotSet';
      v_child_node  := 'item[1]/column[2]';
      v_i           := 1;
      execute immediate 'select ' || pi_column_name || ' from ' ||
                        pi_table_name || ' where rownum=1'
        into v_name_xml;
      select existsnode(v_name_xml,
                        '/PivotSet/item[' || to_char(v_i) || ']/column[1]')
        into v_existsnode
        from dual;
      while v_existsnode = 1 loop
        execute immediate 'select substr(extractvalue(' || pi_column_name ||
                          ', ''/PivotSet/item[' || to_char(v_i) || ']/column[1]''),1,30)
        from ' || pi_table_name || ' x'
          into v_name;
        v_sql := v_sql || '"' || v_name || '" varchar2(30) path ''item[' ||
                 to_char(v_i) || ']/column[2]'',';
        v_i   := v_i + 1;
        select existsnode(v_name_xml,
                          '/PivotSet/item[' || to_char(v_i) || ']/column[1]')
          into v_existsnode
          from dual;
      end loop;
      v_sql := trim(',' from v_sql) || ') x';
      commit;
      select count(1)
        into v_count
        from user_tab_columns
       where table_name = upper(pi_create_table);
      if v_count = 0 then
        execute immediate 'create table ' || pi_create_table || ' as ' || v_sql;
      end if;
    end;

     

    第一个参数为要解析xml文件所属数据表,第二个参数为要解析xml所存字段,第三个参数存放解析后的数据集。

    测试:

    begin

    ljz_pivot_xml_sp('(select * from (select deptno,sal from emp) pivot xml(sum(sal) for deptno in(any)))',

    'deptno_xml',

    'ljz_pivot_tmp');

    end;

    初学oracle xml解析,这种方法较为笨拙,一个一个循环列,原型如下:

    select extractvalue(name_xml, '/PivotSet/item[1]/column[1]')

    from (select * from (select name,nums from demo) pivot xml(sum(nums) for name in(any))) x

    where existsnode(name_xml, '/PivotSet/item[1]/column[1]') = 1;

    select x.*

    from (select *

    from (select name, nums from demo)

    pivot xml(sum(nums)

    for name in(any))) a,

    xmltable('/PivotSet' passing a.name_xml columns

    芒果 varchar2(30) path 'item[1]/column[2]',

    苹果 varchar2(30) path 'item[2]/column[2]') x

    不知是否存在直接进行解析的方法,这种方法还不如直接行列转变,不通过xml转来转去。

    select '''' || listagg(substr(name, 1, 30), q'{','}') within group(order by name) || ''''

    from (select distinct name from demo);

    select *

    from (select name, nums from demo)

    pivot(sum(nums)

    for name in('苹果', '橘子', '葡萄', '芒果'));

    这样拼接字符串反而更加方便。

    结论

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

    分类: Oracle 相关

    好文要顶 关注我 收藏该文  

    mellowsmile
    关注 - 3
    粉丝 - 24

    +加关注

    3

    1

    « 上一篇:Oracle发送邮件,支持HTML,多收件人,多附件
    » 下一篇:Oracle用法、函数备忘记录

    posted @ 2015-07-13 10:54 mellowsmile 阅读(134290) 评论(7) 编辑 收藏

    https://www.cnblogs.com/mellowsmile/p/4642306.html

     

     

     

     

     

     

     

    https://blog.csdn.net/wyqwilliam/article/details/82559023

    SQL行转列、列转行

    2018年09月09日 15:51:14 道法—自然 阅读数:182

    SQL行转列、列转行

    这个主题还是比较常见的,行转列主要适用于对数据作聚合统计,如统计某类目的商品在某个时间区间的销售情况。列转行问题同样也很常见。

    一、整理测试数据

     
    1. create table wyc_test(

    2. id int(32) not null auto_increment,

    3. name varchar(80) default null,

    4. date date default null,

    5. scount int(32),

    6. primary key (id)

    7. );

    8. INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (1,'小说','2013-09-01',10000);

    9. INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (2,'微信','2013-09-01',20000);

    10. INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (3,'小说','2013-09-02',30000);

    11. INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (4,'微信','2013-09-02',35000);

    12. INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (5,'小说','2013-09-03',31000);

    13. INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (6,'微信','2013-09-03',36000);

    14. INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (7,'小说','2013-09-04',35000);

    15. INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (8,'微信','2013-09-04',38000);

    16. INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (9,'小说','2013-09-01',80000);

    17. INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (10,'微信','2013-09-01',70000);

    二、行转列

    主要思路是分组后使用case进行条件判断处理

     
    1. #行转列

    2. select

    3. a.date,

    4. sum(case a.name

    5. when '小说' then a.scount

    6. else 0

    7. end) 'sum_小说',

    8. max(case a.name

    9. when '小说' then a.scount

    10. else 0

    11. end) 'max_小说',

    12. sum(case a.name

    13. when '微信' then a.scount

    14. else 0

    15. end) '微信',

    16. max(case a.name

    17. when '小说' then a.scount

    18. else 0

    19. end) 'max_微信'

    20. from

    21. wyc_test a

    22. group by date;

    结果:

    三、列转行

    主要思路也是分组后使用case

     
    1. #列转行

    2. select

    3. a.date,

    4. concat('小说:',

    5. cast(sum(case a.name

    6. when '小说' then a.scount

    7. else 0

    8. end)

    9. as char),

    10. '微信',

    11. cast(sum(case a.name

    12. when '微信' then a.scount

    13. else 0

    14. end)

    15. as char)) as 'str'

    16. from

    17. wyc_test a

    18. group by a.date;

    19. #列转行

    20. #1.使用mysql提供的函数分组

    21. select a.date,group_concat(a.name,'总量:', a.scount) from wyc_test a group by a.date,a.name;

    22. #2.使用mysql提供的函数分组

    23. select a.date,a.name, group_concat(a.name, '总量:', a.scount) from wyc_test a group by a.date,a.name;

    24. #3.普通group结合字符串拼接

    25. SELECT

    26. a.date,

    27. concat('小说总量:',

    28. cast(sum(case a.name

    29. when '小说' then a.scount

    30. else 0

    31. end)

    32. as char)) as '小说',

    33. concat('微信总量:',

    34. cast(sum(case a.name

    35. when '微信' then a.scount

    36. else 0

    37. end)

    38. as char)) as '微信'

    39. from

    40. wyc_test a

    41. group by a.date;

    结果:

    四、列转行详解
     
    1.1、初始测试数据
            表结构:TEST_TB_GRADE2
    Sql代码 
    create table TEST_TB_GRADE2 

      ID         NUMBER(10) not null, 
      USER_NAME  VARCHAR2(20 CHAR), 
      CN_SCORE   FLOAT, 
      MATH_SCORE FLOAT, 
      EN_SCORE   FLOAT 

     
            初始数据如下图:

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

                          
     
    这就是最常见的列转行,主要原理是利用SQL里面的union,具体的sql语句如下:
    Sql代码 
    select user_name, '语文' COURSE , CN_SCORE as SCORE from test_tb_grade2  
    union select user_name, '数学' COURSE, MATH_SCORE as SCORE from test_tb_grade2  
    union select user_name, '英语' COURSE, EN_SCORE as SCORE from test_tb_grade2  
    order by user_name,COURSE  
     
     也可以利用【 insert all into ... select 】来实现,首先需要先建一个表TEST_TB_GRADE3:
    Sql代码 
    create table TEST_TB_GRADE3   
        (  
          USER_NAME VARCHAR2(20 CHAR),   
          COURSE    VARCHAR2(20 CHAR),   
          SCORE     FLOAT   
        )   
     再执行下面的sql:
     
    Sql代码 
    insert all 
    into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, '语文', CN_SCORE) 
    into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, '数学', MATH_SCORE) 
    into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, '英语', EN_SCORE) 
    select user_name, CN_SCORE, MATH_SCORE, EN_SCORE from test_tb_grade2; 
    commit; 

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

     

     收藏 

     分享

    https://blog.csdn.net/wyqwilliam/article/details/82559023

     

     

     

     

     

     

    https://www.cnblogs.com/wayne-ivan/p/6416489.html

    博客园  首页  新随笔  联系  管理  订阅 订阅

    wm_concat函数的排序问题

    wm_concat在行转列的时候非常有用,但在行转列的过程中的排序问题常常难以控制。

    可见下面例子:
    准备测试表:
    drop table t;
    create table t (n number,m number);
    insert into t values(1,1);
    insert into t values(5,3);
    insert into t values(3,3);
    insert into t values(6,5);
    insert into t values(7,2);
    insert into t values(2,2);
    insert into t values(0,1);
    insert into t values(11,1);
    insert into t values(15,3);
    insert into t values(13,3);
    insert into t values(16,5);
    insert into t values(17,2);
    insert into t values(12,2);
    insert into t values(10,1);
    commit;

    SQL> select * from t order by 2,1;

    N M
    ———- ———-
    0 1
    1 1
    10 1
    11 1
    2 2
    7 2
    12 2
    17 2
    3 3
    5 3
    13 3
    15 3
    6 5
    16 5

    测试wm_concat后的顺序:
    测试1:
    SQL> select m,wm_concat(n) from t group by m;

    M WM_CONCAT(N)
    ———- ——————————————————————————–
    1 11,0,1,10
    2 17,2,7,12
    3 15,3,5,13
    5 16,6

    可见wm_concat后的顺序并没有按大->小,或小->大的顺序。

    测试2:
    –参考网上一些解决思路
    SQL> select m,wm_concat(n)
    2 from (select n,m from t order by m,n )
    3 group by m;

    M WM_CONCAT(N)
    ———- ——————————————————————————–
    1 0,11,10,1
    2 2,17,12,7
    3 3,15,13,5
    5 6,16

    可见顺序问题还是没有解决

    最终解决思路:
    SQL> select m, max(r)
    2 from (select m, wm_concat(n) over (partition by m order by n) r from t)
    3 group by m ;

    M MAX(R)
    ———- ——————————————————————————–
    1 0,1,10,11
    2 2,7,12,17
    3 3,5,13,15
    5 6,16

    分类: Oracle

    好文要顶 关注我 收藏该文  

    伊凡
    关注 - 0
    粉丝 - 86

    +加关注

    0

    0

    « 上一篇:Oracle树查询及相关函数
    » 下一篇:Oracle系统表整理+常用SQL语句收集

    posted @ 2017-02-19 18:23 伊凡 阅读(8665) 评论(0) 编辑 收藏

    https://www.cnblogs.com/wayne-ivan/p/6416489.html

     

     

     

     

     

     

    https://blog.csdn.net/seandba/article/details/72730657

    Oracle SQL函数pivot、unpivot转置函数实现行转列、列转行

    2017年05月25日 16:18:27 Seandba 阅读数:26664

     版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/Seandba/article/details/72730657

    函数PIVOT、UNPIVOT转置函数实现行转列、列转行,效果如下图所示:

    1.PIVOT为行转列,从图示的左边到右边

    2.UNPIVOT为列转行,从图示的右边到左边

    3.左边为纵表,结构简单,易扩展

    4.右边为横表,展示清晰,方便查询

    5.很多时候业务表为纵表,但是统计分析需要的结果如右边的横表,这时候就需要用到转置函数了

    示例图表:

     

    Pivot语法:

     

     
    1. SELECT ....

    2. FROM <table-expr>

    3. PIVOT

    4. (

    5. aggregate-function(<column>)

    6. FOR <pivot-column> IN (<value1>, <value2>,..., <valuen>)

    7. ) AS <alias>

    8. WHERE .....

     

    注意:

    FOR <pivot-column>

    这个是不支持表达式的,如果需要,请通过子查询或者视图先预处理。

     

     

    Pivot

    例子1:先构造一个子查询,然后根据CHANNEL列进行转置,源表sales_view里面可能有很多列,不需要列先通过子查询过滤掉再进行转置。

     

    另外转置后的列指定了别名,值是对amount_sold列的汇总。

     

     
    1. SELECT * FROM

    2. (SELECT product, channel, amount_sold

    3. FROM sales_view

    4. ) S PIVOT (SUM(amount_sold)

    5. FOR CHANNEL IN (3 AS DIRECT_SALES, 4 AS INTERNET_SALES,

    6. 5 AS CATALOG_SALES, 9 AS TELESALES))

    7. ORDER BY product;

    8.  
    9. PRODUCT DIRECT­_SALES INTERNET_SALES CATALOG_SALES TELESALES

    10. ---------------------- ------------ -------------- ------------- ---------

    11. ...

    12. Internal 6X CD-ROM 229512.97 26249.55

    13. Internal 8X CD-ROM 286291.49 42809.44

    14. Keyboard Wrist Rest 200959.84 38695.36 1522.73

    15. ...


    例子2:基于多列进行转置,下面例子是基于channel、quarter两列进行转置

     

     

     
    1. SELECT *

    2. FROM

    3. (SELECT product, channel, quarter, quantity_sold

    4. FROM sales_view

    5. ) PIVOT (SUM(quantity_sold)

    6. FOR (channel, quarter) IN

    7. ((5, '02') AS CATALOG_Q2,

    8. (4, '01') AS INTERNET_Q1,

    9. (4, '04') AS INTERNET_Q4,

    10. (2, '02') AS PARTNERS_Q2,

    11. (9, '03') AS TELE_Q3

    12. )

    13. );

    14.  
    15. PRODUCT CATALOG_Q2 INTERNET_Q1 INTERNET_Q4 PARTNERS_Q2 TELE_Q3

    16. ------- ---------- ----------- ----------- ----------- -------

    17. ...

    18. Bounce 347 632 954

    19. ...

    20. Smash Up Boxing 129 280 560

    21. ...

    22. Comic Book Heroes 47 155 275

    23. ...


    例子3:对多列的值进行汇总计算,以下是基于channel例进行转置,然后对amount_sold和quantity_sold两列进行合计运算

     

     

     
    1. SELECT *

    2. FROM

    3. (SELECT product, channel, amount_sold, quantity_sold

    4. FROM sales_view

    5. ) PIVOT (SUM(amount_sold) AS sums,

    6. SUM(quantity_sold) AS sumq

    7. FOR channel IN (5, 4, 2, 9)

    8. )

    9. ORDER BY product;

    10.  
    11. PRODUCT 5_SUMS 5_SUMQ 4_SUMS 4_SUMQ 2_SUMS 2_SUMQ 9_SUMS 9_SUMQ

    12. ------------- ------ ------ ------ ------ ------ ------ ------ ------

    13. O/S Doc Set English 142780.36 3081 381397.99 8044 6028.66 134

    14. O/S Doc Set French 55503.58 1192 132000.77 2782

    15. ...

     

     

     

    Unpivot

    unpivot是pivot的相反操作,进行的是列转行

     

     

    例子1:先看源表结构,for子句指定将(Q1_SUMQ, Q2_SUMQ, Q3_SUMQ, Q4_SUMQ)这4列转置为行,

    for子句之前的quantity_sold是4列转置后的列名,

    decode还定义了每列转置为行后新标示列的值,这个等下看第2个例子可以看到,也可以在 in 子句后面加 as 指定别名。

    UNPIVOT INCLUDE NULLS 指定空值也进行转置,如果是EXCLUDE NULLS 将忽略空值。

     

     

     
    1. SELECT *

    2. FROM pivotedTable

    3. ORDER BY product;

    4.  
    5. PRODUCT Q1_SUMQ Q1_SUMA Q2_SUMQ Q2_SUMA Q3_SUMQ Q3_SUMA Q4_SUMQ Q4_SUMA

    6. --------------- ------- ------- ------- -------- ------- -------- ------- ---------

    7. 1.44MB External 6098 58301.33 5112 49001.56 6050 56974.3 5848 55341.28

    8. 128MB Memory 1963 110763.63 2361 132123.12 3069 170710.4 2832 157736.6

    9. 17" LCD 1492 1812786.94 1387 1672389.06 1591 1859987.66 1540 1844008.11

     

     
    1. SELECT product, DECODE(quarter, 'Q1_SUMQ', 'Q1', 'Q2_SUMQ', 'Q2', 'Q3_SUMQ', 'Q3',

    2. 'Q4_SUMQ', 'Q4') AS quarter, quantity_sold

    3. FROM pivotedTable

    4. UNPIVOT INCLUDE NULLS

    5. (quantity_sold

    6. FOR quarter IN (Q1_SUMQ, Q2_SUMQ, Q3_SUMQ, Q4_SUMQ))

    7. ORDER BY product, quarter;

    8.  
    9. PRODUCT QUARTER QUANTITY_SOLD

    10. ------- -- -------------

    11. 1.44MB External 3.5" Diskette Q1 6098

    12. 1.44MB External 3.5" Diskette Q2 5112

    13. 1.44MB External 3.5" Diskette Q3 6050

    14. 1.44MB External 3.5" Diskette Q4 5848

    15. 128MB Memory Card Q1 1963

    16. 128MB Memory Card Q2 2361

    17. 128MB Memory Card Q3 3069

    18. 128MB Memory Card Q4 2832

    19. ...

     

     

    例子2:转置多列的情况

     

     
    1. SELECT product, quarter, quantity_sold, amount_sold

    2. FROM pivotedTable

    3. UNPIVOT INCLUDE NULLS

    4. (

    5. (quantity_sold, amount_sold)

    6. FOR quarter IN ((Q1_SUMQ, Q1_SUMA) AS 'Q1', (Q2_SUMQ, Q2_SUMA) AS 'Q2', (Q3_SUMQ, Q3_SUMA) AS 'Q3', (Q4_SUMQ, Q4_SUMA) AS 'Q4'))

    7. ORDER BY product, quarter;

    8.  
    9. PRODUCT QU QUANTITY_SOLD AMOUNT_SOLD

    10. ----------------------------- -- ------------- ------------

    11. 1.44MB External 3.5" Diskette Q1 6098 58301.33

    12. 1.44MB External 3.5" Diskette Q2 5112 49001.56

    13. 1.44MB External 3.5" Diskette Q3 6050 56974.3

    14. 1.44MB External 3.5" Diskette Q4 5848 55341.28

    15. 128MB Memory Card Q1 1963 110763.63

    16. 128MB Memory Card Q2 2361 132123.12

    17. 128MB Memory Card Q3 3069 170710.4

    18. 128MB Memory Card Q4 2832 157736.6


    总结,基本上按照语法套用即可,注意将源表非相关列先过滤掉,可是是子查询,也可以是视图。

    最后试试解决这个问题吧,看你是否真的懂了!

    http://blog.csdn.net/seandba/article/details/72629724

     

    以上内容均来自Oracle11g官方文档,我只是搬运工。。。

    Oracle® Database Data Warehousing Guide
    11g Release 2 (11.2)

    E25554-01

     

    阅读更多  收藏 

     分享

    https://blog.csdn.net/seandba/article/details/72730657

     

     

     

     

     

     

    https://segmentfault.com/q/1010000009622091?sort=created

    Oracle pivot in子查询的疑问

    在使用pivot函数进行行转列的时候 in后面接子查询就报错

     

    按照oracle的文档,pivot语句中in后面的列如果不固定,只能使用xml格式的返回结果,如:
    http://www.oracle-developer.n...

    SQL> SELECT *
      2  FROM   pivot_data
      3  PIVOT  XML
      4        (SUM(sal) FOR deptno IN (ANY));
    
    JOB       DEPTNO_XML
    --------- ---------------------------------------------------------------------------
    ANALYST   <PivotSet><item><column name = "DEPTNO">20</column><column name = "SUM(SAL)
              ">6600</column></item></PivotSet>
    
    CLERK     <PivotSet><item><column name = "DEPTNO">10</column><column name = "SUM(SAL)
              ">1430</column></item><item><column name = "DEPTNO">20</column><column name
               = "SUM(SAL)">2090</column></item><item><column name = "DEPTNO">30</column>
              <column name = "SUM(SAL)">1045</column></item></PivotSet>
    
    MANAGER   <PivotSet><item><column name = "DEPTNO">10</column><column name = "SUM(SAL)
              ">2695</column></item><item><column name = "DEPTNO">20</column><column name
               = "SUM(SAL)">3272.5</column></item><item><column name = "DEPTNO">30</colum
              n><column name = "SUM(SAL)">3135</column></item></PivotSet>

    如果不想要xml格式的结果,就只能使用把in之间的内容提前计算出来,动态拼接成sql语句,使用execute immediately动态执行。

    •  评论 · 2
    • 赞赏
    • 编辑

    邢爱明  4.8k

    2017-06-05 回答

    https://segmentfault.com/q/1010000009622091?sort=created

     

     

     

     

     

     

    https://zhidao.baidu.com/question/2203556378984153268.html?qbl=relate_question_0&word=pivot%20for%20in%20%D6%D0%BB%B9%BF%C9%D2%D4%D6%B8%B6%A8%D7%D3%B2%E9%D1%AF

     

    oracle中pivot子查询如何用 50

    在oracle中使用pivot函数,看介绍说在in()中可以使用子查询,但是使用子查询执行时报错缺少表达式,请问子查询应该如何使用,是不是pivot不支持子查询,谢谢

     我来答

    分享

    举报浏览 6787 次

    • 你的回答被采纳后将获得:
    • 系统奖励15(财富值+成长值)+难题奖励20(财富值+成长值)+提问者悬赏50(财富值+成长值)

    1个回答

    #今日科普# 又到公司年会,有哪些给力的小游戏?

    chengccy2010 
    来自电脑网络类芝麻团 2015-05-22

    xml 类型的时候可以使用any 关键字和子查询,返回的结果是xml结构的;

    select * from (
    select times_purchased as "Puchase Frequency", state_code
    from customers t
    )
    pivot xml
    (
    count(state_code)
    for state_code in (select state_code from preferred_states)
    )
    order by 1

     本回答被网友采纳

     7   67

     评论 

    分享

     举报

     

    https://zhidao.baidu.com/question/2203556378984153268.html?qbl=relate_question_0&word=pivot%20for%20in%20%D6%D0%BB%B9%BF%C9%D2%D4%D6%B8%B6%A8%D7%D3%B2%E9%D1%AF

     

     

     

     

     

     

    展开全文
  • mysql中的greatest 函数和 least函数 转:https://www.cnblogs.com/moyijian/p/10029236.html 博客园作者转载时作了扩充,比原作多了一点参考. greatest(max(one),max(two),max(three)) 求多列的最大值,oracle...
  • pivot unpivot In this article, we’ll walk-through the SQL Pivot and SQL Unpivot operators and how they can be useful to transpose SQL Server data. Also, we’ll discuss both static and dynamic ways ...
  • sql unpivot 我最近在一个匿名用户的Stack Overflow上遇到一个非常有趣的问题 。 问题是关于使用表值函数在Oracle中生成以下形式的表: Description COUNT ------------------- TEST1 10 TEST2 15 TEST3 25 TEST4...
  • Pivot 和 Unpivot 使用简单的 SQL 以电子表格类型的交叉表报表显示任何关系表中的信息,并将交叉表中的所有数据存储到关系表中。 如您所知,关系表是表格化的,即,它们以列-值对的形式出现。假设一个表名为 ...
  • MYSQL字符串类型的行列转换

    千次阅读 2019-04-04 16:36:03
    MYSQL 的行列转换的问题 一般接触过比较大的统计数据表会有相关数据 作为支撑...MySQL非数字类的聚合讲述下自己的实验过程,MSSQL和ORACLE 分别不一样 MSSQL 有关键字pivot/unpivot 进行转换 oracle case when 语句...
  • PIVOT用于将列值旋转为列名(即行转列),在SQL Server2000可以用聚合函数配合CASE语句实现 PIVOT的一般语法是:PIVOT(聚合函数(列) FOR 列 in (…) )AS P 完整语法: ...UNPIVOT用于将列明转为列值
  • 点击上方蓝字关注我们在数据分析中,窗口函数是我们经常用到的函数,今天的文章我们总结了常用的各类窗口函数并给出实例。一 创建数据集from pyspark.sql import SprkS...
  • 在数据分析中,窗口函数是我们经常用到的函数,今天的文章我们总结了常用的各类窗口函数并给出实例。一.创建数据集from pyspark.sql import SparkSession im...
  • 这里,我将使用Pivot和unpivot来实现看似复杂的功能。这个功能在SQL2005及以上版本才有。 MSDN解释: 可以使用PIVOT和UNPIVOT关系运算符将表值表达式更改为另一个表。PIVOT通过将表达式某一列中的唯一值转换为输出...
  • org.springframework.jdbc.UncategorizedSQLException:sql injection violation RPAREN, actual LPAREN unpivotsql语句注入失败,使用的是oracle 数据库,在plsql里面能执行,放到项目中报错。select distinct s....
  • MySQL---行转列

    2021-03-02 13:52:34
    1、SqlServer和Orcle中可以使用pivot行转列函数快速实现,而MySQL中没有 pivot: SELECT * FROM student PIVOT ( SUM(score) FOR subject IN (语文, 数学, 英语) ) 2、MySQL实现行转列 SELECT name, MAX( CASE ...
  • 1.1 语法:PIVOT(聚合函数(列) FOR 列 in (…) )AS P 1.2 测试数据 CREATE TABLE [StudentScores] ( [UserName] NVARCHAR(20), --学生姓名 [Subject] NVARCHAR(30), --科目 [Score] FLOAT, --成绩 ) INSERT ...
  • 分析函数

    2019-09-23 05:55:49
    分析函数基本语法函数名称([参数]) OVER (PARTITION BY 子句 字段,....[ORDER BY 子句 字段,..[ASC][DESC][NULLS FIRST][NULLS LAST]][WINDOWING 子句]);使用PARTITION BY子句 SELECT deptno,ename,sal,job, SUM...
  • 1.简介 PIVOT通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,并在必要时对最终输出中所...UNPIVOT与PIVOT执行相反的操作,将表值表达式的列转换为列值。 通俗简单的说:PIVOT就是行转列,UNPI
  • SQL Server中行列转换 Pivot UnPivot PIVOT用于将列值旋转为列名(即行转列),在SQL Server 2000可以用聚合函数配合CASE语句实现 PIVOT的一般语法是:PIVOT(聚合函数(列) FOR 列 in (…) )AS P 完整语法:...
  • mysql常识

    2018-08-03 22:23:53
    fulltext全文索引(mysql新功能) unique唯一性索引 primary主键索引 唯一索引和主键的区别 唯一索引的字段可为空,主键不可为空 mysql 中UNIQUE KEY 到底是约束还是索引 1.两者关系 unique索引包含了...
  • [ INTO new_table ] FROM [table_nameA join_type|apply_type|pivot|unpivot]| ]table_nameB [ON |AS] [ WHERE search_condition ]  [ GROUP BY group_by_expression ]  [ HAVING search_condition ]  ...
  • |‘EFG’ --S:可以视作spark sql中的concat函数 concat(‘ABC’, ‘EFG’) 3、oracle中的wm_concat() --O:oracle中的wm_concat() 独有的,mysql中有一个group_concat()函数 --实现行转列功能,即将查询出的某一列值...
  • MySQL没有专门的行转列 一、经典行转列 1.行转列 将此表 转化为以下格式 xingmign 代码 SELECT 姓名,MAX(case 课程 when '语文' THEN 分数 END) as 语文, MAX(case 课程 when '数学' THEN 分数 END)...
  • 一,开窗函数:为了解决复杂的子查询引入进来的,开窗函数也是对行集组进行聚合计算的,并且它返回是多个值,目前oracle db2 sqlserver都支持,但是mysql不支持 1.row number() over partition by :分组排名 ...
  • MySQL面试

    2021-07-26 01:13:43
    高阶函数 第二个就是分组排序并添加序号,我们有时候需要对表中的数据进行分组,并根据组内信息进行重新编号,这样的操作就是分组编号排序, select ROW_NUMBER() over(partition by t3.customerId order by t3.uid) as ...
  • SQL Server数据库中,PIVOT在帮助中这样描述滴:可以使用 PIVOT 和UNPIVOT 关系运算符将表值表达式更改为另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,并在必要时对最终输出...
  • Mysql实现 1、Mysql行转列 在接下来的例子中,我新建了一个学生成绩表,表SQL如下: DROP TABLE IF EXISTS `stu`; CREATE TABLE `stu` ( `id` int(5) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT ...
  • mysql常识整理

    2018-03-13 16:31:36
    fulltext全文索引(mysql新功能)unique唯一性索引primary主键索引唯一索引和主键的区别唯一索引的字段可为空,主键不可为空mysql 中UNIQUE KEY 到底是约束还是索引1.两者关系 unique索引包含了unique约束,因为unique...
  • 1. 在数据库中你怎么把时间进行格式化 select DATE_FORMAT( updated_at, '%y-%M-%D' ) from tb_sku; ...1. 视图 : 就是一种虚拟的表 ,是一个...可以使用 PIVOT,UNPIVOT比较快速实现行转列,列转行,而且可扩展性强

空空如也

空空如也

1 2 3 4 5 ... 8
收藏数 151
精华内容 60
关键字:

mysqlunpivot函数

mysql 订阅