Oracle行转列和列转行查询

行转列

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