在看Tom的11 things about 11gr2.ppt，里面提到要实现列转行的统计:
1. 9i里面要自己实现统计函数

2. 10g里有个sys_connect_by_path可用

SQL> select deptno,
  2         substr(
  3         max(sys_connect_by_path(ename, ‘; ‘)),
  4         3) enames
  5    from (
  6  select deptno,
  7         ename,
  8         row_number()
  9         over
 10         (partition by deptno
 11          order by ename) rn
 12    from emp
 13         )
 14   start with rn = 1
 15  connect by prior deptno = deptno
 16      and prior rn+1 = rn
 17    group by deptno
 18    order by deptno
 19  /

DEPTNO ENAMES
———- ——————–
        10 CLARK; KING; MILLER
        20 ADAMS; FORD; JONES;
           SCOTT; SMITH
 
        30 ALLEN; BLAKE;
           JAMES; MARTIN;
           TURNER; WARD

3. 11gr2里提供了listagg更方便
SQL> select deptno,
  2         listagg( ename, ‘; ‘ )
  3         within group
  4         (order by ename) enames
  5    from emp
  6   group by deptno
  7   order by deptno
  8  /

    DEPTNO ENAMES
———- ——————–
        10 CLARK; KING; MILLER
        20 ADAMS; FORD; JONES;
           SCOTT; SMITH

        30 ALLEN; BLAKE;
           JAMES; MARTIN;
           TURNER; WARD