SELECT regexp_substr('公司1,贵公司2', '[^,]+', 1, LEVEL, 'i')

FROM dual

CONNECT BY LEVEL <= length('公司1,贵公司2') - length(REPLACE('公司1,贵公司2', ',', '')) + 1

以上为字符串带分隔符的转换为列

Oracle11.2新增了LISTAGG函数，可以用于字符串聚集，测试如下：

1,版本

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

2，测试数据

SQL> 

SQL> select empno,ename,deptno from scott.emp;

EMPNO ENAME DEPTNO

----- ---------- ------

7369 SMITH 20

7499 ALLEN 30

7521 WARD 30

7566 JONES 20

7654 MARTIN 30

7698 BLAKE 30

7782 CLARK 10

7788 SCOTT 20

7839 KING 10

7844 TURNER 30

7876 ADAMS 20

7900 JAMES 30

7902 FORD 20

7934 MILLER 10

14 rows selected

3，作为聚集函数

SQL> SELECT deptno,

2 LISTAGG(ename, ',') WITHIN GROUP(ORDER BY ename) AS employees

3 FROM scott.emp

4 GROUP BY deptno;

DEPTNO EMPLOYEES

------ --------------------------------------------------------------------------------

10 CLARK,KING,MILLER

20 ADAMS,FORD,JONES,SCOTT,SMITH

30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

SQL> 

--更换排序列

SQL> SELECT deptno,

2 LISTAGG(ename, ',') WITHIN GROUP(ORDER BY hiredate) AS employees

3 FROM scott.emp

4 GROUP BY deptno;

DEPTNO EMPLOYEES

------ --------------------------------------------------------------------------------

10 CLARK,KING,MILLER

20 SMITH,JONES,FORD,SCOTT,ADAMS

30 ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES

--order by必须存在

SQL> SELECT deptno,

2 LISTAGG(ename, ',') WITHIN GROUP() AS employees

3 FROM scott.emp

4 GROUP BY deptno;

SELECT deptno,

LISTAGG(ename, ',') WITHIN GROUP() AS employees

FROM scott.emp

GROUP BY deptno

ORA-30491: ORDER BY 子句缺失

SQL> SELECT deptno,

2 LISTAGG(ename, ',') WITHIN GROUP(order by null) AS employees

3 FROM scott.emp

4 GROUP BY deptno;

DEPTNO EMPLOYEES

------ --------------------------------------------------------------------------------

10 CLARK,KING,MILLER

20 ADAMS,FORD,JONES,SCOTT,SMITH

30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

==〉按字母顺序排列

4，LISTAGG作为分析函数使用

SQL> SELECT empno,

2 ename,

3 deptno,

4 LISTAGG(ename, ',') WITHIN GROUP(ORDER BY ename) over(partition by deptno) AS employees

5 FROM scott.emp;

EMPNO ENAME DEPTNO EMPLOYEES

----- ---------- ------ --------------------------------------------------------------------------------

7782 CLARK 10 CLARK,KING,MILLER

7839 KING 10 CLARK,KING,MILLER

7934 MILLER 10 CLARK,KING,MILLER

7876 ADAMS 20 ADAMS,FORD,JONES,SCOTT,SMITH

7902 FORD 20 ADAMS,FORD,JONES,SCOTT,SMITH

7566 JONES 20 ADAMS,FORD,JONES,SCOTT,SMITH

7788 SCOTT 20 ADAMS,FORD,JONES,SCOTT,SMITH

7369 SMITH 20 ADAMS,FORD,JONES,SCOTT,SMITH

7499 ALLEN 30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

7698 BLAKE 30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

7900 JAMES 30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

7654 MARTIN 30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

7844 TURNER 30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

7521 WARD 30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

14 rows selected

5,其他实现方法参考

--model

SQL> SELECT deptno, vals

2 FROM (SELECT deptno, RTRIM(vals, ',') AS vals, rn

3 FROM scott.emp MODEL PARTITION BY(deptno) DIMENSION BY(ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY ename) AS rn) MEASURES(CAST(ename AS VARCHAR2(4000)) AS vals) RULES(vals [ ANY ] ORDER BY rn DESC = vals [ CV() ] || ',' || vals [ CV() + 1 ]))

4 WHERE rn = 1

5 ORDER BY deptno;

DEPTNO VALS

---------- --------------------------------------------------------------------------------

10 CLARK,KING,MILLER

20 ADAMS,FORD,JONES,SCOTT,SMITH

30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

--表函数：WMSYS.WM_CONCAT，10G已经提供该函数

SQL> 

SQL> SELECT deptno, WMSYS.WM_CONCAT(ename) AS vals --<-- WM_CONCAT ~= STRAGG

2 FROM scott.emp

3 GROUP BY deptno;

DEPTNO VALS

------ --------------------------------------------------------------------------------

10 CLARK,MILLER,KING

20 SMITH,FORD,ADAMS,SCOTT,JONES

30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD