Wednesday, September 2, 2009

11gR2 - LISTAGG

I was looking at the Oracle 11gR2 New Features guide and came across the new LISTAGG analytic function. Oh boy, oh boy! I've used Tom Kyte's stragg function for ages to do string concatenation of multiple row values. I'm way happy that that function is now "built in".

The syntax is:

LISTAGG (measure_expression [, 'delimiter_expression'])
WITHIN GROUP (order_by_clause) [OVER query_partition_clause]


Here are two examples from the docs:

SELECT LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date, last_name) "Emp_list",
MIN(hire_date) "Earliest"
FROM employees
WHERE department_id = 30;

Emp_list Earliest
------------------------------------------------------------ ---------
Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares 07-DEC-02



SELECT department_id "Dept.",
LISTAGG(last_name, '; ')
WITHIN GROUP (ORDER BY hire_date) "Employees"
FROM employees
GROUP BY department_id;

Dept. Employees
------ ------------------------------------------------------------
10 Whalen
20 Hartstein; Fay
30 Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares
40 Mavris
50 Kaufling; Ladwig; Rajs; Sarchand; Bell; Mallin; Weiss; Davie
s; Marlow; Bull; Everett; Fripp; Chung; Nayer; Dilly; Bissot
; Vollman; Stiles; Atkinson; Taylor; Seo; Fleaur; Matos; Pat
el; Walsh; Feeney; Dellinger; McCain; Vargas; Gates; Rogers;
Mikkilineni; Landry; Cabrio; Jones; Olson; OConnell; Sulliv
an; Mourgos; Gee; Perkins; Grant; Geoni; Philtanker; Markle
60 Austin; Pataballa; Lorentz; Ernst; Hunold
70 Baer
...

After I read it in the NF guide, I Googled and found lots more folks talking about it.

Cool stuff! No telling what else I'll find...better keep reading!

6 comments:

Kerry Osborne said...

Hi Karen,

Listagg is a cool addition that's long over due. I have been meaning to read through the doc's on the new analytic functions but haven't had the time yet. Have you seen anything else in there that has peeked your interest?

Good to see you're posting again (and getting settled in).

Kerry

Karen said...

The analytic function enhancements are the stuff that I'm most intrigued by. But, I'm also anxious to play around with the recursive WITH capabilities. I'm just trying to figure out how I'd apply it. I've got a crazy example that computes Fibonacci numbers, but that's not overly practical. :) Recursive code typically hasn't been very performant so I'm wondering if this new WITH capability does a better job.

流浪他乡 said...

Hi Karen,

I've used Tom Kyte's stragg function. I am just wondering if there is any performance benefit to move to this great new built-in function LISTAGG?

Thanks,
Xingsheng

Karen said...

Xingsheng,

There will absolutely be performance improvements. Instead of using user-defined objects with member functions called via an outer function wrapper, it's now a "built-in".

Joel said...

Any idea how to de-listagg?

Unknown said...

Oracle has included WM_CONCAT() throughout release 10g and 11g R1, although it is undocumented. I'm pretty sure it's a direct implementation of STRAGG() but is built in already.

Any idea if LISTAGG() can handle CLOBs? WM_CONCAT() and STRAGG() will fall apart if the result exceeds 4000 bytes.

-Miles