oracle数据库字段拆分+多行拼接

1.字段切割，一条数据变多行

select a.*,
	regexp_substr(a.to_spilt_col,'[^,]+',1,level) as new_col 
from tab_name a 
connect by a.id= prior a.id 
and prior dbms_random.value is not null
and level <= regexp_count(a.to_spilt_col,'[^,]+')
--to_spilt_col 待切割字段
--new_col 切割后的字段名
--[^,]+ 根据,去切割

2.一个字段多行数据拼接成一行

select listagg(a.to_join_col,',') within group(order by a.id) as new_col
from tab_name a 
group by a.group_col
--to_join_col 需要拼接的字段
--group_col 分根据分组的字段

3.切割后再拼接

select b.id,
	listagg(c.name,',') as new_join_col
from(
	select a.*
		regexp_substr(a.to_spilt_col,'[^,]+',1,level) as new_col 
	from tab_name a 
	connect by a.id= prior a.id 
	and prior dbms_random.value is not null
	and level <= regexp_count(a.to_spilt_col,'[^,]+')
) b
left join c on c.code = b.code
group by b.id