将表emp输出'10 clark king miller'

摘自：http://tkyte.blogspot.com/

Suppose someone asks you:

I need to take the results of a query and pivot a value. That is, I would like output from the EMP table to look like this:

DEPTNO ENAMES
------- ------------
10 clark king miller
20 adams ford …
…
can this be done in just SQL?

With the addition of analytic functions in Oracle 8i Release 2 and the SYS_CONNECT_BY_PATH() function in Oracle 9i Release 1 – this became something we can in fact do rather easily in SQL. The approach we will take is:

1. Partition the data by DEPTNO and within each DEPTNO sort the data by ENAME and assign a sequential number using the ROW_NUMBER() analytic function
2. So, we end up with a record eventually that is the result of connecting 1 to 2 to 3 to 4 and so on for each DEPTNO
3. The SYS_CONNECT_BY_PATH() function will return the list of ENAMES concatenated together for us.

The query would look like this:

SQL> select deptno,
  2         max(sys_connect_by_path(ename, ' ' )) scbp
  3    from (select deptno, ename, 
                    row_number() over 
                   (partition by deptno order by ename) rn
  4            from emp
  5             )
  6  start with rn = 1
  7  connect by prior rn = rn-1 and prior deptno = deptno
  8  group by deptno
  9  order by deptno
 10  /

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

I used to use STRAGG for this (prior to sys_connect_by_path and analytics) - but now find this approach preferable.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-82996/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-82996/