oracle中SYS.ODCIVARCHAR2LIST()实现行转列
SYS.ODCIVARCHAR2LIST:
SELECT COLUMN_VALUE FROM TABLE(SYS.ODCIVARCHAR2LIST('1','2','3','4','5'));
Oracle 10G 以上版本才支持SYS.ODCIVARCHAR2LIST,其实SYS.ODCIVARCHAR2LIST只不过是一个TYPE,
所以在9I版本中可以通过创建一个TYPE来使用该功能:
CREATE OR REPLACE TYPE MY_ODCIVARCHAR2LIST AS VARRAY(32767) OF VARCHAR2(4000);
SELECT COLUMN_VALUE FROM TABLE(MY_ODCIVARCHAR2LIST('1','2','3','4','5'));
总结:(1)Table函数将数组里的内容通过SQL语句查询出来;
(2)ODCIVARCHAR2LIST 在9I 及以上版本中均可使用。在9I中可通过创建TYPE,10G及以上直接使用SYS.ODCIVARCHAR2LIST;
(3)ODCIVARCHAR2LIST 适用于字符集,不适用单个字符串,如果是单个字符串,可以通过参考如下方法实现。
其他方法实现行转列(仅供参考)
(1) 利用CONNECT BY (使用9I,10G,11G,12C)
WITH T AS (SELECT '1,2,3,4,5' AS STR FROM DUAL)
SELECT STR1
FROM ( SELECT DISTINCT
SUBSTR(T.CA,INSTR(T.CA, ',', 1, C.LV) + 1,
INSTR(T.CA, ',', 1, C.LV + 1) -(INSTR(T.CA, ',', 1, C.LV) + 1)) AS STR1
FROM (SELECT ',' || STR || ',' AS CA,LENGTH(STR || ',') -NVL(LENGTH(REPLACE(STR, ',')), 0) AS CNT FROM T) T,
(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 9) C
WHERE C.LV <= T.CNT
ORDER BY STR1);
(2).正则表达式(使用10G及以上版本)
WITH TEST AS (SELECT '1,2,3,4,5' AS STR FROM DUAL)
SELECT DISTINCT REGEXP_SUBSTR(STR, '[^,]+', 1, LEVEL)
FROM TEST
CONNECT BY ROWNUM <= 5;