LISTAGG function in Oracle

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 ‘,’.

Please follow and like us:
Content Protection by DMCA.com