oracle 列转行函数listagg、判断函数decode

1、decode

使用decode判断字符串是否一样

DECODE(value,if1,then1,if2,then2,if3,then3,...,else)

decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)

某些场景可以代替case when

 

2、listagg

首先看下数据情况

 想把这个人的阶段列转成一行分隔显示使用listagg函数

select
    listagg (decode(sts,'A',coll_stage),',') within group (order by coll_stage desc) coll_stage_a,
    listagg (decode(sts,'I',coll_stage),',') within group (order by coll_stage desc) coll_stage_i,
    listagg (coll_stage,',') within group (order by coll_stage desc) coll_stage_all
from lc_contcoll_rel where cont_no = 'HT90201804121600006690493' group by cont_no

结果：