1.列转行
select a.*,trim(regexp_substr(lanwei,'[^,]+',1,level)) as lanwei
from biao a
connect by ID = prior ID
and prior dbms_random.value is not null
and level <= length(regexp_replace(lanwei, '[^,]'))+1
2.行转列
SELECT regexp_replace(listagg(PRODUCT, '|') within
group(order by PRODUCT),
'([^\|]+)(\|\1)*(\||$)',
'\1\3') as TEXT
FROM M_SUBCON_MATERIAL_INFO
where 1=1
group by PART_NUMBER