行转列
SELECT REGEXP_SUBSTR(A.ROLECODE, '[^,]+', 1, L) ROLECODE
FROM (SELECT '93271,93273,93327' ROLECODE FROM DUAL) A,
(SELECT LEVEL L FROM DUAL CONNECT BY LEVEL <= 100) B
WHERE L <= LENGTH(A.ROLECODE) - LENGTH(REPLACE(ROLECODE, ',')) + 1;
with a as (select '93271,93273,93327' d from dual)
select regexp_substr(d,'[^,]+',1,rownum) id from a
connect by rownum <= length(regexp_replace(d,'[^,]+'))+1
列转换
select name, coures, to_char(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
1-100
SELECT LEVEL l FROM DUAL CONNECT BY LEVEL <= 100