How to combine multiple rows of a column while deduping the values in the column? [duplicate]

Question

How can I get the result specified below?

I tried using the LISTAGG function, but it does not eliminate duplicates.

Is there a way to eliminate duplicates at the database itself instead of fetching the values into the service and then deduping them?

Dataset:

col0   col1
1      7
1      8
1      8
1      2
1      9
2      9
3      10
3      11
3      12

Desired result:

col0   col1
1      2,7,8,9
2      9
3      10,11,12

Attempted SQL (that does not eliminate duplicates):

SELECT 
  col0, 
  SUM(col2 + col3) AS mycount
  LISTAGG(col1, ',') within GROUP (ORDER BY col1) 
FROM mytable 
WHERE 
  _time >= TIMESTAMP '2024-12-03 00:00:00' AND
  _time <  TIMESTAMP '2024-12-03 01:00:00'
GROUP BY col0
ORDER BY mycount DESC
LIMIT 10
;

add the DISTINCT keyword to the LISTAGG function

Paul W
– Paul W

2024-12-04 23:13:49 +00:00
Commented Dec 4, 2024 at 23:13 — Paul W
– Paul W, Commented Dec 4, 2024 at 23:13

samhita · Accepted Answer · 2024-12-04 23:14:25Z

1

You need to use DISTINCT within LISTAGG

Fiddle

SELECT col0,
       LISTAGG(DISTINCT col1, ',') WITHIN GROUP (ORDER BY col1) AS col1
FROM test
GROUP BY col0;

Output

answered Dec 4, 2024 at 23:14

samhita

4,2512 gold badges12 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to combine multiple rows of a column while deduping the values in the column? [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related