CODEWITHSUNDEEP
sqloracle-database
4 Leave Cover
4 Leave Cover

Reputation: 1276

distinct listagg in Oracle sql

Please refer to the image attached.

enter image description here

I have data as shown below, by using the query I am able to get the result as follow. However, I want the actual result to be distinct in Col_2. That means X1 will only appear once, followed by X2 and X3. I do not want the same value to be repeated more than once in the column.

Is it possible to achieve?

Upvotes: 3

Views: 871

Answers (2)

user15676
user15676

Reputation: 153

tried adding DISTINCT in the LISTAGG function

SELECT COL_1, LISTAGG(DISTINCT(COL2), ', ') 
                WITHIN GROUP (ORDER BY COL2) AS COL_2
       SUM(COL_3)
FROM TABLE 1
GROUP BY COL_1

Upvotes: 0

Jorge Campos
Jorge Campos

Reputation: 23381

Try this way:

SELECT A.COL_1, 
       LISTAGG(A.COL_2, ',') 
          WITHIN GROUP(ORDER BY A.COL_2) AS COL_2, 
       SUM(A.SS) AS COL_3
  FROM (SELECT COL_1, COL_2, SUM(COL_3) SS
          FROM TEST
         GROUP BY COL_1, COL_2 ) A
 GROUP BY COL_1

See it here on fiddle: http://sqlfiddle.com/#!4/ee5f2/1

Upvotes: 5

Related Questions