ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle 11g 新聚集函数listagg实现列转行

Oracle 11g 新聚集函数listagg实现列转行

原创 Linux操作系统 作者:chncaesar 时间:2013-08-06 14:23:42 0 删除 编辑
先上语法:
LISTAGG ( column | expression, 
delimiter ) WITHIN GROUP (ORDER BY column | expression)
这是一个聚集函数。通过一个例子来说明其用法:
SELECT department_id, LISTAGG(last_name, '; ') 
WITHIN GROUP (ORDER BY hire_date, last_name) "Emp_list"
FROM employee
group by department_id;

10	Eckhardt; Newton; Friedli; James; Michaels; Dovichi
20	peterson; leblanc
30	Jeffrey; Wong
	Newton
可以看到简单一行代码实现了列转行功能。在这之前需要用decode()穷举各种可能来实现,假如无法穷举需要写一大段函数(详见asktom.oracle.com)。
在这个例子里,last_name根据department_id聚集,同一department_id下所有last_name作为一组出现。

listagg作为分析函数



SELECT department_id "Dept", hire_date "Date", last_name "Name",
LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date, last_name)
OVER (PARTITION BY department_id) as "Emp_list"
FROM employee
ORDER BY "Dept", "Date", "Name";
这段SQL查询每个部门里每个人,他的入职日期,所有同部门的同事。按照部门,入职日期,员工姓名排序。

10 2004/07/07 Eckhardt Eckhardt; Newton; Friedli; James; Michaels; Dovichi
10 2006/09/24 Newton Eckhardt; Newton; Friedli; James; Michaels; Dovichi
10 2007/05/16 Friedli Eckhardt; Newton; Friedli; James; Michaels; Dovichi
10 2007/05/16 James Eckhardt; Newton; Friedli; James; Michaels; Dovichi
10 2007/05/16 Michaels Eckhardt; Newton; Friedli; James; Michaels; Dovichi
10 2011/07/07 Dovichi Eckhardt; Newton; Friedli; James; Michaels; Dovichi
20 2008/11/03 peterson peterson; leblanc
20 2009/03/06 leblanc peterson; leblanc
30 2010/02/27 Jeffrey Jeffrey; Wong
30 2010/02/27 Wong Jeffrey; Wong
2005/09/14 Newton Newton



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

上一篇: linux Find命令
请登录后发表评论 登录
全部评论

注册时间:2013-07-30

  • 博文量
    102
  • 访问量
    899768