先上语法：

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博客

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