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_listfrom 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函数