CODEWITHSUNDEEP
sqloracle-databaseselect
Isuru
Isuru

Reputation: 722

Getting duplicate values as a list in oracle db

I have following table in a oracle db

id       group
100       1
101       2
102       2
103       1
104       1

I need to get the ids as a comma separated list grouped by group as below.

group       ids
  1       100,103,104
  2       101,102

Upvotes: 0

Views: 33

Answers (1)

Mureinik
Mureinik

Reputation: 311373

Oracle 11g introduced the listagg function:

SELECT   "group", LISTAGG(id, ',') WITHIN GROUP (ORDER BY id) AS ids
FROM     my_table
GROUP BY "group"

Note: group is a reserved word in SQL, hence the quotes to protect it as a column name. If your real table has a different name, you won't need them.

Upvotes: 1

Related Questions