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

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; Dovichi20    peterson; leblanc30    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查询每个部门里每个人，他的入职日期，所有同部门的同事。按照部门，入职日期，员工姓名排序。

102004/07/07EckhardtEckhardt; Newton; Friedli; James; Michaels; Dovichi
102006/09/24NewtonEckhardt; Newton; Friedli; James; Michaels; Dovichi
102007/05/16FriedliEckhardt; Newton; Friedli; James; Michaels; Dovichi
102007/05/16JamesEckhardt; Newton; Friedli; James; Michaels; Dovichi
102007/05/16MichaelsEckhardt; Newton; Friedli; James; Michaels; Dovichi
102011/07/07DovichiEckhardt; Newton; Friedli; James; Michaels; Dovichi
202008/11/03petersonpeterson; leblanc
202009/03/06leblancpeterson; leblanc
302010/02/27JeffreyJeffrey; Wong
302010/02/27WongJeffrey; Wong
2005/09/14NewtonNewton