Oracle 11g 新聚集函数listagg实现列转行 先上语法：LISTAGG ( column | expression, delimiter ) WITHIN GROUP (ORDER BY colum 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; 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查询每个部门里每个人，他的入职日期，所有同部门的同事。按照部门，入职日期，员工姓名排序。
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
推荐阅读：
Oracle函数之Replace() Oracle函数大全15位身份证升18位身份证的Oracle函数