CODEWITHSUNDEEP
sqloracle-databaseviewlistagg
danny libren
danny libren

Reputation: 137

distinct listagg in sql view

I have a table like this:

id  name
1   ben
1   ben
2   charlie
2   dan
2   edgar
3   frank

and i want the following columns to be part of an existing view in sql developer:

id  names
1   ben
2   charlie,dan,edgar
3   frank

I was able to generate the table (id, names) using listagg but i cant add it to the view (most popular error is invalid identifier).

please let me know if more information is needed.

thanks

Upvotes: 1

Views: 728

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49112

Use a sub-query to first select only the DISTINCT rows before applying LISTAGG.

For example,

Setup

SQL> CREATE TABLE t
  2      (id int, name varchar2(7));

Table created.

SQL>
SQL> INSERT ALL
  2      INTO t (id, name)
  3           VALUES (1, 'ben')
  4      INTO t (id, name)
  5           VALUES (1, 'ben')
  6      INTO t (id, name)
  7           VALUES (2, 'charlie')
  8      INTO t (id, name)
  9           VALUES (2, 'dan')
 10      INTO t (id, name)
 11           VALUES (2, 'edgar')
 12      INTO t (id, name)
 13           VALUES (3, 'frank')
 14  SELECT * FROM dual;

6 rows created.

SQL>

Query

SQL> WITH DATA AS(
  2  SELECT DISTINCT ID, NAME FROM t
  3  )
  4  SELECT ID,
  5    listagg(NAME, ',') WITHIN GROUP (
  6  ORDER BY ID) NAME
  7  FROM DATA
  8  GROUP BY ID;

        ID NAME
---------- -----------------------------
         1 ben
         2 charlie,dan,edgar
         3 frank

SQL>

Another way is to remove the duplicates from the aggregated values as answered here https://stackoverflow.com/a/27817597/3989608.

For example,

SQL> SELECT id,
  2    RTRIM(REGEXP_REPLACE(listagg (name, ',') WITHIN GROUP (
  3  ORDER BY id), '([^,]+)(,\1)+', '\1'), ',') name
  4  FROM t
  5  GROUP BY id;

        ID NAME
---------- ---------------------------------------------
         1 ben
         2 charlie,dan,edgar
         3 frank

SQL>

Upvotes: 1

Related Questions