Home > oracle functions > String Aggregate function –LISTAGG

String Aggregate function –LISTAGG

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.

Categories: oracle functions
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: