oracle 9i 中行转列

示例如下：

 SELECT deptno  
          , LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))  
             KEEP (DENSE_RANK LAST ORDER BY curr),',') AS concatenated  
      FROM   ( SELECT deptno  
                    , ename  
                    , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr  
                    , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev  
               FROM   emp )  
      GROUP BY deptno 
     CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno 
      START WITH curr = 1;  

　　

显示如下：

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