This Oracle built-in function is used to put all the selected rows from one column into comma-separated values. Then, after having this conversion, this can be stored into a variable for further use.
Let's take a look at one example,
Suppose we have a table called dealpricing, where we have the list of deals,
and we can see the following,
If we want to simply use LISTAGG with the deal names, we do the following,
With that, we will be able to see the following,
Obviously, we have much more deals in this table, the limitation of rownum less than 5 is just for illustrative purposes. Now, let's say we want to sort the deals per deal type, and we want all the deals to show up in one row as comma-separated-values, according to the corresponding deal type. For that, we do the following,
With that, we are telling Oracle to get the first 50 deals sorted by creation date in ascending order, as comma-separated-values, and broken down by deal type. The result looks like this,
Another explanation,
LISTAGG is a powerful function in Oracle PL/SQL used for string aggregation. It allows you to concatenate multiple values from a column into a single string, separated by a specified delimiter. Here's how to use LISTAGG in Oracle PL/SQL:
SELECT LISTAGG(column_name, delimiter) WITHIN GROUP (ORDER BY order_column) AS concatenated_string
FROM table_name
WHERE conditions;
Let's break down the components:
column_name: This is the column whose values you want to concatenate.delimiter: This is the separator you want to use between the concatenated values.order_column: If you want the concatenated values to be ordered in a specific way, you can use this clause.table_name: The table name from which you want to retrieve the values.conditions: Any filtering conditions you want to apply to the selection.
Here's an example:
Assume you have a table named students with columns student_id and course_name, and you want to list each student's course name, separated by commas.
SELECT student_id, LISTAGG(course_name, ', ') WITHIN GROUP (ORDER BY course_name) AS courses
FROM students
GROUP BY student_id;
In this example, the LISTAGG function concatenates the course_name values for each student, separated by commas, and orders them alphabetically. The result set will have one row for each student, showing their student_id and the concatenated courses.
Remember that the concatenated string's length is limited by the maximum length of a VARCHAR2 column (4000 characters in most cases). If your concatenated string might exceed this limit, you might need to handle it using other techniques, like using XML functions in combination with XMLAGG.
Hope you all have a nice day!!!
