我需要通过逗号将一个列的值分开。
例如:BCDEY
; 我需要将其转换为B, C, D, E, Y
。
以下是“选择”:
SELECT CDGRUPOCONDICAO FROM TBINTCLIENTE;
with cad as
(select 'BCDEY' cad from dual)
select regexp_replace (regexp_replace(cad,'(.)','\1, '),', $','') cad_comma from cad;
with testdata as (select 'BCDEY' str from dual)
select listagg(c, ', ') within group(order by lvl)
from (
select substr(str, level, 1) c,
level lvl
from testdata
connect by level <= length(str)
)
B, C, D, E, Y
listagg
通过使用', '
将这些项重新组合起来。另一种解决方案,使用递归子查询因式分解(因此,假定Oracle >= 11g release 2):
with testdata as (select 1 id, 'BCDEY' str from dual union all
select 2, 'ABC' from dual),
-- ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-- replace that subquery by your actual query
splited(c,r,id,lvl) as (
select '' c, str r, id, 0 lvl from testdata
union all
select substr(r, 1, 1) c,
substr(r, 2) r,
id,
lvl+1
from splited
where r is not null)
select listagg(c, ', ') within group(order by lvl)
from splited
group by (id)
LISTAGG(C,',')WITHINGROUP(ORDERBYLVL)
B, C, D, E, Y
A, B, C