CODEWITHSUNDEEP
sqlpostgresql
Sajal
Sajal

Reputation: 106

SQL query to get distinct values from grouped records of each column

Suppose, I have a songs table

Table name: Songs
- id
- artist
- composer
- lyricist

I want to write a sql query so that I can get distinct values from the records, which are grouped by id. Example:

Id Artist Composer Lyricist
1  a1      c1       l1
1  a2      c1       l1
1  a1      c2       l1
2  a3      c1       l2
2  a4      c1       l3

The query should return

Id Artist Composer Lyricist
1  a1, a2  c1, c2   l1
2  a3, a4  c1       l2, l3

If I can get a generic SQL query, i.e., not specific to certain database functionality, it would be great. Please note that, the columns may differ in different table, so it's better, if I don't have to write each column name in the query.

Please, if anyone can suggest something.

Upvotes: 0

Views: 987

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

The Postgres SQL version uses string_agg() or array_agg():

select id,
       string_agg(distinct artist, ','),
       string_agg(distinct composer, ','),
       string_agg(distinct lyricist, ',')
from t
group by id;

The generic SQL version is called listagg().

Upvotes: 1

Related Questions