篇首语:本文由小编为大家整理,主要介绍了oracle 逗号分割,列转行,行转列 转载相关的知识,希望对你有一定的参考价值。
https://www.cnblogs.com/gdzhong/p/4726315.html?tvd
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
以上是关于oracle 逗号分割,列转行,行转列 转载的主要内容,如果未能解决你的问题,请参考以下文章