Trending September 2023 # How Listagg Works In Pl/Sql With Examples? # Suggested October 2023 # Top 14 Popular

You are reading the article How Listagg Works In Pl/Sql With Examples? updated in September 2023 on the website Lifecanntwaitvn.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 How Listagg Works In Pl/Sql With Examples?

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.

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

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.

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

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;

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.