PL/SQL listagg

Definition of PL/SQL listagg

PL/SQL provides the different types of functions to the user, in which that listagg () is one of the functions provided by the PL/SQL. Normally listagg () is a function that is used to concatenate the different values of columns for every group based on the order by clause. In another word, we can say that it is a single set aggregate function that means it works on all rows from the table and generates the single row as per our requirement, in which it is grouped by the data and we specify the order by clause and then finally concatenate all data.

Syntax

select listagg (specified colm name [, ‘required delimiter’]) within group (order by clause[specified colm name]) from specified table name;

Explanation

In the above syntax, we use the select clause and listagg () function with different parameters as follows.

• specified colm name: It is a specified column name or expression whose we need to concatenate together as final output. If it contains the null then it is ignored.
• Required delimiter: It is an optional part of this syntax, basically it is used to separate the value of the column as per requirement.
• Group by: the group by is used to make the group of different rows.
• Order by clause: It is used to determine the order of concatenated values.

How listagg works in PL/SQL?

Now let’s see how the listagg () function works in PL/SQL as follows.

The LISTAGG logical capacity was presented in Oracle 11g Release 2, making it exceptionally simple to total strings. The decent thing about this capacity is it likewise permits us to arrange the components in the linked rundown. On the off chance that you are utilizing 11g Release 2, you should utilize this capacity for string accumulation.
For a predetermined measure, LISTAGG orders information inside each gathering determined in the ORDER BY proviso and afterward links the upsides of the action segment. As a binary set total capacity, LISTAGG works on all columns and returns a binary yield line. As a gathering set total, the capacity works on and returns a yield column for each gathering characterized by the GROUP BY statement. As an insightful capacity, LISTAGG allotments the question result set into bunches dependent on at least one articulation in the query_partition_clause.
The contentions to the capacity are dependent upon the accompanying guidelines:
The column name can be any articulation. Invalid qualities in the action section are disregarded.
The delimiter assigns the string that is to isolate the action esteems. This proviso is discretionary and defaults to NULL.
The order_by_clause decides the request where the connected qualities are returned. The capacity is deterministic just if the ORDER BY segment list accomplished exceptional requests.
The return information type is RAW if the action section is RAW; in any case, the return esteem is VARCHAR2.
Now let’s see how we can handle the overflow condition of the listagg () function as follows.
In Oracle Database 12c Release 2 we can add the ON OVERFLOW TRUNCATE statement to deal with flood mistakes effortlessly. Naturally, the shortening is an ellipsis (‘…’) and a check of the flood characters is incorporated.

Examples

Now let’s see different examples of the listagg () in PL/SQL for better understanding as follows. First, we need to create the new table by using the create table statement as follows.

create table s_dept(dept_id number(20), s_name varchar2(30), City varchar2(30));

Explanation

In the above example, we use create table statements to create a new table name as s_dept with different attributes such as dept_id, s_name, and City with different data types as shown. The final output of the above statement we illustrated by using the following screenshot as follows.

Now we need to insert some records into the newly created table by using insert into statement as follows.

insert into s_dept(dept_id, s_name, City) values(10,'Jenny','Hongking');
insert into s_dept(dept_id, s_name, City) values(10,'Rohit','London');
insert into s_dept(dept_id, s_name, City) values(20,'Virat','Singpore');
insert into s_dept(dept_id, s_name, City) values(10,'Sanjay','Mumbai');
insert into s_dept(dept_id, s_name, City) values(20,'Johan','Pune');
select * from s_dept;

Explanation

By using insert into the statement we inserted some records. The final output of the above statement we illustrated by using the following screenshot as follows.

Now we can perform the listagg () function as follows.

select listagg(s_name, ',') within group (order by s_name) as s_name2 from s_dept;

Explanation

In the above example, we use the listagg () function to show all student names in a single row and it is separated by using a comma as shown in the above statement. In the above statement we use the select with listagg () function, inside the bracket we need to provide the column name with a delimiter. After that, we need to group and order by clause as per our requirement. The final output of the above statement we illustrated by using the following screenshot as follows.

Now let’s see another example of the listagg () function as follows.

select dept_id, listagg(s_name, ',') within group (order by s_name) as s_name2 from s_dept group by dept_id;

Explanation

In this example we try to implement the listagg () function, in the above statement, we use the select with listagg () function, inside the bracket we need to provide the column name with a delimiter. After that, we need to group and order by clause as per our requirement. In this example, we show the student name with the respective department id and it is separated by using a comma as shown in the above statement. The final output of the above statement we illustrated by using the following screenshot as follows.

Now let’s see one more example of the listagg () function.

select dept_id, listagg(s_name, ',') within group (order by s_name) as s_name2 from s_dept group by dept_id order by dept_id;

Explanation

By using the above statement we implement the listagg () function. In this example, we show the student name that belongs to each department and it is ordered by dept_id as shown. The final output of the above statement we illustrated by using the following screenshot as follows.

Conclusion

We hope from this article you learn PL/SQL listagg function. From the above article, we have learned the basic syntax of listagg function and we also see different examples of listagg function. From this article, we learned how and when we use PL/SQL listagg function.

Recommended Articles

This is a guide to PL/SQL listagg. Here we discuss the definition, syntax, How listagg works in PL/SQL? and examples respectively. You may also have a look at the following articles to learn more –

1. PL/SQL ALTER TABLE
2. PL/SQL NVL
3. PL/SQL GROUP BY
4. PL/SQL Cursor Loop