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:

<code class="language-sql">SELECT col1, LISTAGG(col2, ', ') WITHIN GROUP (ORDER BY col2) AS "names"
FROM table_x
GROUP BY col1</code>

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:

<code class="language-sql">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;
/</code>

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

<code class="language-sql">SELECT col1, get_comma_separated_value(col1) FROM table_name</code>

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:

<code class="language-sql">SELECT col1, GROUP_CONCAT(col2) FROM table_name GROUP BY col1</code>

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!