oracle官网行转列,Oracle 行转列

蔚落 2023-01-18 15:30 160阅读 0赞

一、简易运用

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

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

)

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;

内容来源于网络如有侵权请私信删除

oracle官网行转列,Oracle 行转列

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 oracle 行转列和列转行的几种方式

相关 oracle官网行转列,Oracle 行转列

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

相关 Oracle 行转列动态出转换的列

相关 Oracle 行转列常用的方法

相关 Oracle行转列和列转行

相关 Oracle 正则表达式行转列

相关 Oracle最常见的行转列

相关 oracle关键字pivot行转列

相关 oracle行转列 vmsys.vm_concat