Oracle 将逗号分隔的列转行 和 将行转成 逗号分隔的列
将逗号分隔的列转行
第一种方法:
需求:按ID,把PARTICIPATORS字段里的内容按‘,’ 分开,转成行
select id,PARTICIPATORS from (with test as (select id ,PARTICIPATORS from NSC_OA_RW where PARTICIPATORS is not null ) select id,substr(t.cPARTICIPATORS,instr(t.cPARTICIPATORS, ',', 1, c.lv) + 1, instr(t.cPARTICIPATORS, ',', 1, c.lv + 1) - (instr(t.cPARTICIPATORS, ',', 1, c.lv) + 1)) AS PARTICIPATORS from (select id,',' || PARTICIPATORS || ',' AS cPARTICIPATORS,length(PARTICIPATORS || ',') - nvl(length(REPLACE(PARTICIPATORS, ',')), 0) AS cnt FROM test) t, (select LEVEL lv from dual CONNECT BY LEVEL <= 100) c where c.lv <= t.cnt )
第二种:listagg
将行转成分组汇总成逗号分隔的列
第一种方法:使用listagg 函数
SELECT formmain_id, '火车' as lx,field0193 as ccsqdh, field0157 as ccrid, LISTAGG(field0200, ',') WITHIN GROUP (ORDER BY field0200) AS batch , sum(nvl(field0174,0)) as je, round(sum(nvl(field0174,0)/1.09),2) as notaxje from formson_52602022327 where nvl(field0174,0) <>0 and field0193='CCSQ-202309-093' --and field0200='easypay_708094_20230201' group by formmain_id,field0193, field0157
但是会有一个问题 LISTAGG 的结果会有重复值
第二种方法:使用wm_concat(distinct 字段)
SELECT formmain_id, '火车' as lx,field0193 as ccsqdh, field0157 as ccrid, wm_concat(distinct field0200) AS batch , sum(nvl(field0174,0)) as je, round(sum(nvl(field0174,0)/1.09),2) as notaxje from formson_52602022327 where nvl(field0174,0) <>0 and field0193='CCSQ-202301-032' --and field0200='easypay_708094_20230201' group by formmain_id,field0193, field0157
这两种办法 都有一个共同的问题这两函数返回的值的字段没有对应的类型,是一段文本。所以在使用该字段关联的时候会报错 ora-00932:数据类型不-致: 应为-.但却获得clob。
解决办法就是 to_char (wm_concat(distinct 字段) ) 、 to_char (LISTAGG(field0200, ',') WITHIN GROUP (ORDER BY field0200))