oracle列转行、列转行

oracle列转行

SELECT REGEXP_SUBSTR('aa,bbb,cccc', '[^,]+', 1, rownum)

from dual 

connect by level <= LENGTH(regexp_replace('aa,bbb,cccc', '[^,]+', ''));

转换前：“aa,bbb,cccc”

转换后：

aa

bbb

cccc

oracle行转列

with baseData as(

select '1' id,'aaa' name from dual

union all

select '2' ,'bbb' from dual

)

SELECT listagg(name ,',') WITHIN GROUP (ORDER BY id)

FROM  baseData ;

转换前：

aaa

bbb

转换后："aaa,bbb"