Oracle 11g 新聚集函式listagg實現列轉行

chncaesar發表於2013-08-06
Oracle函式
先上語法：
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章