Archive

Archive for the ‘oracle functions’ Category

String Aggregate function –LISTAGG

June 5, 2011 Leave a comment

In oracle 11gR2 LISTAGG is a new function which ise used to perform string aggregation.

Example data:

CUSTOMER_ID CUSTOMER_NAME
1 uma
1 poojitha
1 radhika
2 mahesh
2 katru

String aggregation is simply the grouping and concatenation of multiple rows of data into a single row per group

with string aggregation the output looks like below.

CUSTOMER_ID CUSTOMER_NAME_AGGREGATED
1 uma,poojitha,radhika
2 mahesh,katru

listagg syntax overview

The LISTAGG function has the following syntax structure:

LISTAGG( [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )]

LISTAGG is an aggregate function that can optionally be used as an analytic (i.e. the optional OVER() clause). The following elements are mandatory:

  • the column or expression to be aggregated;
  • the WITHIN GROUP keywords;
  • the ORDER BY clause within the grouping.

SQL>SELECT customer_id, LISTAGG(customer_name, ‘,’) WITHIN GROUP (ORDER BY customer_name) AS customer_name_aggregated
FROM   listagg_test
GROUP BY customer_id;
CUSTOMER_ID CUSTOMER_NAME_AGGREGATED
———– ——————————
1 poojitha,radhika,uma
2 katru,mahesh

SQL> SELECT customer_id, LISTAGG(customer_name, ‘,’) WITHIN GROUP () AS customer_name_aggregated
FROM   listagg_test
GROUP BY customer_id;  2    3
SELECT customer_id, LISTAGG(customer_name, ‘,’) WITHIN GROUP () AS customer_name_aggregated
*
ERROR at line 1:
ORA-30491: missing ORDER BY clause

From the above we can conclude that order clause is manadatory.If the order clause is not required for then you can specify like below.

SQL> SELECT customer_id, LISTAGG(customer_name, ‘,’) WITHIN GROUP (ORDER BY  NULL) AS customer_name_aggregated
FROM   listagg_test
GROUP BY customer_id;

CUSTOMER_ID CUSTOMER_NAME_AGGREGATED
———– ——————————
1 poojitha,radhika,uma
2 katru,mahesh

The new LISTAGG function is the fastest technique for string aggregation and has the additional benefit of being a simple built-in function.

Advertisements
Categories: oracle functions
%d bloggers like this: