How to Achieve GROUP_CONCAT Functionality in Oracle?

Group Join Operation in Oracle

MySQL's GROUP_CONCAT function can concatenate the grouped values ​​of multiple rows into a string. There is no exact equivalent function in Oracle, but there are several ways to achieve similar functionality.

Oracle 11g and higher uses LISTAGG

Oracle 11g introduced the LISTAGG function, which aggregates values ​​into comma-separated strings:

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

Oracle 10g and lower versions use custom functions

For Oracle 10g and lower, you can create a custom function:

<code class="language-sql">CREATE OR REPLACE FUNCTION get_comma_separated_value (input_val IN 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>

How to use:

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

Note: There is an (unsupported) function WM_CONCAT available in some older Oracle versions.

Alternative method in MySQL (for comparison)

In MySQL you can use the GROUP_CONCAT function:

<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 GROUP_CONCAT Functionality in Oracle?. For more information, please follow other related articles on the PHP Chinese website!