How Can I Ensure Distinct Values When Using Oracle's LISTAGG Function?

Oracle LISTAGG: Generating Strings with Unique Values

Oracle's LISTAGG function is invaluable for concatenating column values into a single string. However, obtaining only distinct values within the aggregated string requires a specific approach. This guide demonstrates efficient methods, avoiding the need for custom functions or stored procedures.

Oracle 19c and Later: Leveraging DISTINCT Directly

For Oracle 19c and subsequent versions, the DISTINCT keyword can be seamlessly integrated within the LISTAGG function itself:

<code class="language-sql">SELECT LISTAGG(DISTINCT the_column, ',') WITHIN GROUP (ORDER BY the_column)
FROM the_table;</code>

Oracle 18c and Earlier: Employing a Subquery

In older Oracle versions (18c and earlier), a subquery is necessary to filter for unique values before aggregation:

<code class="language-sql">SELECT LISTAGG(the_column, ',') WITHIN GROUP (ORDER BY the_column)
FROM (
    SELECT DISTINCT the_column
    FROM the_table
) t;</code>

Incorporating Multiple Columns

The following query extends the functionality to include multiple columns, demonstrating a robust method for more complex scenarios:

<code class="language-sql">SELECT DISTINCT col1, listagg(col2, ',') OVER (PARTITION BY col1 ORDER BY col2) AS col2_list
FROM table_name
WHERE RN = 1
GROUP BY col1
ORDER BY col1;</code>

In conclusion, using either the DISTINCT keyword (19c and above) or a subquery (18c and below), you can effectively generate LISTAGG results containing only unique values. These techniques offer flexibility across different Oracle versions and facilitate the inclusion of multiple columns when required.

The above is the detailed content of How Can I Ensure Distinct Values When Using Oracle's LISTAGG Function?. For more information, please follow other related articles on the PHP Chinese website!