oracle将信息横向,行转列，将表中某字段所有值横向显示

表字段如下SQL> select ename from scott.emp;

ENAME

----------

SMITH

ALLEN

WARD

JONES

MARTIN

BLAKE

CLARK

SCOTT

KING

TURNER

ADAMS

JAMES

FORD

MILLER

14 rows selected.

横向显示SELECT ltrim(MAX(sys_connect_by_path(ename, ',')),',') AS path

FROM (SELECT ename, rownum AS rn FROM scott.emp)

START WITH rn = 1

CONNECT BY rn = rownum

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

SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER

或者

select listagg(ename,',') within group(order by ename) ename from (

select 1 as id , ename from scott.emp

)

group by id

ENAME

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

ADAMS,ALLEN,BLAKE,CLARK,FORD,JAMES,JONES,KING,MARTIN,MILLER,SCOTT,SMITH,TURNER,WARD