• Oracle 行转列



    一、简易运用

    ——>没转之前一个主号绑定多个副号的多行输出(像移动的欢乐在线)

    SELECT   f.town_name 镇区,
              f.school_name 学校,
              f.class_name 班级,
              f.student_name 学生,
              b.phone 主号码,
              b.bindphone 绑定的副号码
       FROM   dg_FPS_BINDPHONE b, dg_family f
    WHERE       b.family_id = f.family_id
              AND b.phone = f.phone
              AND f.school_id = 61758
              AND NVL (f.is_test, 0) <> 1;

    ——>转之后的一行多列输出
    (使用分析函数的情况)
       SELECT   town_name,
                school_name,
                class_name,
                student_name,
                phone,
                MAX (DECODE (rn, 1, bindphone)) "亲情号码1",
                MAX (DECODE (rn, 2, bindphone)) "亲情号码2",
                MAX (DECODE (rn, 3, bindphone)) "亲情号码3",
                MAX (DECODE (rn, 4, bindphone)) "亲情号码4",
                MAX (DECODE (rn, 5, bindphone)) "亲情号码5",
                MAX (DECODE (rn, 6, bindphone)) "亲情号码6"
         FROM   (SELECT   f.town_name,
                          f.school_name,
                          f.class_name,
                          f.student_name,
                          b.phone,
                          b.bindphone,
                          COUNT( * )
                             OVER ( PARTITION BY f.town_name,f.school_name,f.class_name,f.student_name,b.phone
                                ORDER BY ROWNUM
                                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                             )
                             rn
                   FROM   dg_fps_bindphone b, dg_family f
                  WHERE       b.family_id = f.family_id  AND  b.phone = f.phone  AND  f.school_id = 61758  AND  NVL (f.is_test, 0) <> 1)
    GROUP BY   town_name,school_name,class_name,student_name,phone;

    (不使用分析函数的情况)
    select t.user_id,t.name,
    max(decode(t.yw_name,'流量包','是','否')) 是否开通流量包,
    max(decode(t.yw_name,'流量包',dt,NULL)) 是否开通流量包,
    max(case when t.yw_name='加餐包' and t.attr_value like '%50元%' then '50元加餐' end) 是否50元加餐,
    max(case when t.yw_name='加餐包' and t.attr_value like '%50元%' then dt end) 五十元加餐时间,
    max(case when t.yw_name='加餐包' and t.attr_value like '%100元%' then '100元加餐' end) 是否100元加餐,
    max(case when t.yw_name='加餐包' and t.attr_value like '%100元%' then dt end) "100元加餐时间",
    max(case when t.yw_name='加餐包' and t.attr_value like '%200元%' then '200元加餐' end) 是否200元加餐,
    max(case when t.yw_name='加餐包' and t.attr_value like '%200元%' then dt end) "200元加餐时间",
    max(case when t.yw_name='加餐包' and t.attr_value is null then '未知加餐包' end) 是否未知加餐包,
    max(case when t.yw_name='加餐包' and t.attr_value is null then dt end) "未知加餐包时间"
    from temp_liut l, liut_temp5 t
    where l.acc_nbr=t.user_id
    group by t.user_id,t.name;


    二、用 wmsys.wm_concat 系统内部函数
         通过运用wmsys系统用户下的wm_concat函数也能达到行列转换效果(一列显示,数据之间以逗号分隔)

    --  一个老师会带多个班级
       SELECT   town.name 镇区,
                s.school_name,
                t.username 教师,
                t.userid 账号,
                t.mphone 联系方式,
                r.name 角色,
                wmsys.wm_concat (c.class_name) 班级
         FROM   area a,
                town,
                qx_sch_role r,xj_school s LEFT JOIN xj_teacher t ON s.id = t.school_id
                   LEFT JOIN tea_class_subject su ON t.id = su.teacher_id
                LEFT JOIN xj_class c ON su.class_id = c.id
        WHERE       a.id = town.area_id
                AND town.id = s.town_id
                AND r.id = t.role_id
                AND a.id = 1
                AND NVL (town.is_test, 0) <> 1
    GROUP BY   town.name,s.school_name,t.username,t.userid,t.mphone,r.name


    三、11.2新函数listagg
    语法:listagg(分割列,分隔符) within group(order by 排序列)
    select listagg(o.rybs, ';') within group(order by o.rybs)
       from gk_xszrr o
      where rownum <= 100;

  • 相关阅读:
    在JavaWeb中使用Log4j步骤
    关于我为什么不再更新博客园了
    windows termial 配置文件
    windows10 命令行修复系统引导
    vscode修改code runner插件默认使用的编译器
    windows下vscode修复c++找不到头文件
    windows下安装mingw-w64
    vscode美化方法以及定制主题插件
    windows下隐藏磁盘分区
    2018 icpc 徐州网络赛 F Features Track
  • 原文地址:https://www.cnblogs.com/bbliutao/p/7575707.html
Copyright © 2020-2023  润新知