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