你的位置:首页 > 数据库

[数据库]oracle 逗号分割,列转行,行转列


SQL代码 列转行

selectREGEXP_SUBSTR(a.rolecode ,'[^,]+',1,l)rolecodefrom (select 'a,aa,aaa' rolecode from dual) a,(SELECT LEVEL l FROM DUAL CONNECT BY LEVEL<=100) bWHERE l <=LENGTH(a.rolecode) - LENGTH(REPLACE(rolecode,','))+1 

 

或者

with a as (select 'ABC,AA,AD,ABD,JI,CC,ALSKD,ALDKDJ' id from dual)select regexp_substr(id,'[^,]+',1,rownum) id from aconnect by rownum <= length(regexp_replace(id,'[^,]+'))

 

SQL代码 行转列

 

select name,coures,to_char(wmsys.wm_concat(xxx.score)) c from   (select '小明' name,'语文' coures,90 score from dual union all select '小明' name,'语文' coures,91 score from dual union all select '小明' name,'数学' coures,90 score from dual union all select '小明' name,'数学' coures,91 score from dual) xxx  group by xxx.name,coures