LISTAGG is one of the vital Analytic functions of Oracle. It is used to concatenates values of a column for each GROUP. The LISTAGG function is supported in the various versions of the Oracle/PLSQL, including, Oracle 12c and Oracle 11g Release 2.
Syntax:
LISTAGG (measure_column, 'delimiter') WITHIN GROUP (order_by_clause) [OVER (query_partition_clause)]
Parameters:
measure_column: It is used to specify the expressions or columns whose values to be concatenated.
‘delimiter’: It is an optional parameter which is used to specify the delimiter to separate the measure_column values.
query_partition_clause: It is also an optional parameter which is used to partition the results into groups.
order_by_clause: It is also an optional parameter which is used to order the data within each partition.
Example:
Students Table:
ID MARKS EXTRA_MARKS 1 80 10 2 100 20 3 95 15 4 75 10 5 85 15
Query:
SELECT LISTAGG( marks, ', ') WITHIN GROUP (ORDER BY marks) "Marks_Listing" FROM students; |
Output:
Marks_Listing 75, 80, 85, 95, 100
Explanation:
Here, the LISTAGG function will sort all of the ‘marks’ in ascending order from the ‘students’ table and since we used a delimiter of ‘,’, it will separate the records with a ‘,’.