How to Concatenate Multiple Rows into a Single Row in Oracle Using LISTAGG?

Concatenating Multiple Rows into a Single One in Oracle

To concatenate multiple rows into a single row in Oracle without using a stored procedure, Oracle users can employ the LISTAGG clause. This clause allows for efficient row consolidation based on a specified delimiter.

Data Scenario:

Consider the following data:

question_id    element_id
1              7
1              8
2              9
3              10
3              11
3              12

Desired Output:

The desired result is to concatenate the element_id values for each question_id, resulting in:

question_id    element_id
1              7,8
2              9
3              10,11,12

Solution with LISTAGG:

From Oracle 11gR2 onwards, the LISTAGG clause can be used to achieve this concatenation:

SELECT question_id,
       LISTAGG(element_id, ',') WITHIN GROUP (ORDER BY element_id)
FROM YOUR_TABLE
GROUP BY question_id;

This query uses the LISTAGG function to concatenate the element_id values within each group defined by the question_id. The ORDER BY clause ensures the values are ordered within each group before concatenation.

Handling Overflow:

Note that if the resulting concatenated string exceeds the maximum length allowed for the data type (e.g., 4000 characters for a VARCHAR2), Oracle versions 12cR2 and later provide options to deal with overflow.

SELECT question_id,
       LISTAGG(element_id, ',') WITHIN GROUP (ORDER BY element_id) OVERFLOW TRUNCATE/ERROR
FROM YOUR_TABLE
GROUP BY question_id;

This query specifies the ON OVERFLOW TRUNCATE/ERROR clause to either truncate or raise an error if the result exceeds the maximum length.

The above is the detailed content of How to Concatenate Multiple Rows into a Single Row in Oracle Using LISTAGG?. For more information, please follow other related articles on the PHP Chinese website!