Remove Duplicates in Oracle LISTAGG

1 year ago
Isabella Edwards
1 minute

There are various methods available in Oracle to eliminate duplicate values when using the LISTAGG function. Here are some commonly used methods:

  1. Utilizing subqueries and the ROW_NUMBER() function:
SELECT LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY column_name) AS result
FROM (
    SELECT column_name, ROW_NUMBER() OVER (PARTITION BY column_name ORDER BY column_name) rn
    FROM table_name
)
WHERE rn = 1;
  1. Utilize the GROUP BY clause:
SELECT LISTAGG(DISTINCT column_name, ',') WITHIN GROUP (ORDER BY column_name) AS result
FROM table_name;
  1. Using the CONNECT BY clause:
SELECT LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY column_name) AS result
FROM (
    SELECT column_name, ROW_NUMBER() OVER (PARTITION BY column_name ORDER BY column_name) rn
    FROM table_name
)
START WITH rn = 1
CONNECT BY PRIOR column_name = column_name AND PRIOR rn = rn-1;

These methods can be chosen based on specific needs to remove duplicate values.

bannerAds