LISTAGG function in SQL

LISTAGG function:

   Ø LISTAGG function used to concatenate multiple row of a string into a single row.

Syntax:

1. LISTAGG(clm_name, ‘Separator’) within group (order by clm_name) from table_name;

Example:

1. Select ename from emp;

Output:

Example: 1  LISTAGG function

1. Select LISTAGG(ename,',') WITHIN GROUP (order by ename) from emp;

Output:

Example: 2

1. Select LISTAGG(ename,';') WITHIN GROUP (order by empno) from emp;

Output:

Explanation:

   v LISTAGG is the keyword

   v Ename column name of the emp table

   v ; is the separator (User can change the separator whatever developer needs)

   v WITHIN GROUP (order by clm_name) default through the syntax

   v Emp table_name.

Disadvantages:

   Ø The problem of using LISTAGG function is, if we trying to concatenate          more than 4000 characters it’s thrown the exception.

Example:

1. SELECT LISTAGG(party_name,',') WITHIN GROUP (order by party_id) FROM hz_parties;

Note: HZ_Parties table have above 4000 characters string in the table so oracle will throw the error.

The output report will be,

Solution:

1. SELECT LISTAGG(party_name, '; ' ON OVERFLOW TRUNCATE '...') WITHIN GROUP (order by party_name)

   FROM HZ_PARTIES;

Explanation:

   Ø ON OVERFLOW TRUNCATE or ON OVERFLOW ERROR is used to truncate the values.

Share: