CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.4string-aggregation
Riya Bansal
Riya Bansal

Reputation: 1301

Comma separated values with all rows from a table

I have a Services table with three columns.

Service 

sr_id lang alias
1     EN    A
1     PA    B
1     HI    C
2     EN    D
2     HI    E

Now, I want to output with each service id, lang and alias column will be concatenation of alias for a service.

Sample Output -

SR_ID   lang   alias
1       EN     A,B,C
1       PA     A,B,C
1       HI     A,B,C
2       EN     D,E
2       HI     D,E

How can I do that with PostgreSQL 9.4?

Upvotes: 1

Views: 4096

Answers (2)

user330315
user330315

Reputation:

If you don't need them sorted, you can use: 

select sr_id, lang, string_agg(alias, ',') over () as alias
from services
order by sr_id;

Otherwise you need:

select s.sr_id, s.lang, t.alias
from services s
join (
   select sr_id, string_agg(alias, ',' order by alias) as alias
   from services 
   group by sr_id
) t on t.sr_id = s.sr_id
order by s.sr_id;

Upvotes: 5

Tajinder
Tajinder

Reputation: 2338

Tried to create similar condition with same sample data. Please check below query

select a.sr_id , a.lang, b.ALIAS from 
SERVICES a  , 
(
select sr_id , string_agg(ALIAS::text,',') ALIAS
from SERVICES
group by sr_id) b
where a.sr_id = b.sr_id;

Upvotes: 0

Related Questions