How Can I Concatenate Multiple Rows into a Single Row in Oracle Without Using a Stored Procedure?

Concatenating Multiple Rows into a Single Row in Oracle Without a Stored Procedure

When working with data in Oracle, there may be instances where you need to combine multiple rows into a single row. Traditionally, this could be achieved using a stored procedure. However, there is a more efficient and straightforward solution using Oracle's LISTAGG clause.

The LISTAGG Clause in Oracle

Introduced in Oracle 11gR2, the LISTAGG clause allows you to concatenate multiple values into a single string. It takes the following syntax:

LISTAGG(expression, delimiter) WITHIN GROUP (ORDER BY column)

• expression: The column or expression you want to concatenate.
• delimiter: The separator you want to use between the values (e.g., a comma).
• column: The column used to order the values within each group.

Example

Consider the following data set:

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

To concatenate the element_id values for each question_id into a single row, we can use the following query:

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

Output

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

Considerations for Large Result Strings

If the resulting string is expected to exceed 4000 characters (the maximum length for a VARCHAR2 data type), you can use the following enhancement introduced in Oracle 12cR2:

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

• ON OVERFLOW TRUNCATE: Truncates the result string if it exceeds the specified length.
• ON OVERFLOW ERROR: Raises an error if the result string exceeds the specified length.

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