How to Achieve MySQL's GROUP_CONCAT Functionality in Oracle?

SQL Group Joins in Oracle: Equivalent of MySQL's GROUP_CONCAT

In MySQL, the GROUP_CONCAT function is widely used to concatenate values ​​grouped by a specific column. Oracle provides similar functionality via an alternative:

LISTAGG function (11g and above)

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

Output:

col1	names
1	a, b
2	c, d, e

Custom functions (10g and lower)

<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>

Query using this function:

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

Note: Use this function with caution as it may encounter memory issues when handling large data sets.

WM_CONCAT function (not supported)

Some older versions of Oracle provide an unsupported function called WM_CONCAT. However, it is recommended to use LISTAGG or a custom function for modern Oracle versions.

MySQL GROUP_CONCAT equivalent

For completeness, the MySQL GROUP_CONCAT query is provided below:

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

The above is the detailed content of How to Achieve MySQL's GROUP_CONCAT Functionality in Oracle?. For more information, please follow other related articles on the PHP Chinese website!