弹冠相庆

November 29, 2009

久等的LISTAGG

Filed under: Uncategorized — Corey @ 10:52
Tags:
在看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

1 Comment »

  1. 真好玩!

    Comment by Kaya — November 29, 2009 @ 16:45 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: