The Oracle LISTAGG function allows you to aggregate or combine string values from multiple rows into a single row.
For example, it turns this data:
Into this data:
Adam, Brad, Carrie
The same data is shown, but it’s in one row and not multiple.
It’s a valuable string manipulation function, and once you know what it is and how it works, you’ll be able to use it in your queries as needed.
So what does it look like?
The function is:
LISTAGG ( measure_expr [, delimiter]) WITHIN GROUP (order_by_clause) [OVER query_partition_clause]
These parameters are:
measure_expr: This is a column or expression that you want to concatenate the values of. In the example above, it was the first_name column. Mandatory.
delimiter: This is the character between each of the measure_expr values. Optional, the default is a comma.
order_by_clause: This is the order that the values from the measure_expr are listed. Mandatory.
query_partition_clause: This allows you to use LISTAGG as an analytic function, allowing you to show LISTAGG in groups for different rows.
One thing to be aware of is that the output of the Oracle LISTAGG function is limited to 4,000 bytes. If you get more than this, you’ll receive an error. The most common way around this that I’ve seen is to write a custom function and use a CLOB.
Watch the video to find out more and see some examples.
For more information about the Oracle LISTAGG function, including all of the SQL shown in this video and the examples, read the related article here: