Does Oracle Have a Function Equivalent to MySQL's `group_concat`?

Oracle and MySQL String Aggregation: Finding the Equivalent to group_concat

Database tasks often require combining multiple rows' data into a single string. MySQL's group_concat function simplifies this process. But how does Oracle achieve the same result?

Oracle's Solutions

Oracle offers several ways to replicate MySQL's group_concat functionality:

For Oracle 11g and later versions, the LISTAGG function provides a direct equivalent:

SELECT col1, LISTAGG(col2, ', ') WITHIN GROUP (ORDER BY col2) AS "names"
FROM table_x
GROUP BY col1

This neatly aggregates col2 values for each col1 group, separating them with ', '. The ORDER BY clause ensures consistent string ordering.

Older Oracle versions (10g and below) require a custom function. Here's an example:

CREATE OR REPLACE FUNCTION get_comma_separated_value (input_val NUMBER)
RETURN VARCHAR2
IS
  return_text VARCHAR2(10000) := NULL;
BEGIN
  FOR x IN (SELECT col2 FROM table_name WHERE col1 = input_val) LOOP
    return_text := return_text || ',' || x.col2 ;
  END LOOP;
  RETURN LTRIM(return_text, ',');
END;
/

This function iterates through rows, appending values to return_text. The LTRIM function removes the leading comma. Usage:

SELECT col1, get_comma_separated_value(col1) FROM table_name

Note that WM_CONCAT existed in some older Oracle versions but is now unsupported.

MySQL's group_concat for Comparison

For clarity, here's the MySQL group_concat syntax:

SELECT col1, GROUP_CONCAT(col2) FROM table_name GROUP BY col1

This concisely aggregates col2 values per col1 group. While it lacks the explicit ordering capability of LISTAGG, it serves a similar purpose.

The above is the detailed content of Does Oracle Have a Function Equivalent to MySQL's `group_concat`?. For more information, please follow other related articles on the PHP Chinese website!