ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 将表emp输出'10 clark king miller'

将表emp输出'10 clark king miller'

原创 Linux操作系统 作者:lfree 时间:2007-03-26 00:00:00 0 删除 编辑

摘自: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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2485
  • 访问量
    6290641