Concat multiple rows PSQL

Question

     id  |     name      |   Subject   | Lectured_Times |     Faculty                      
 3258132 | Chris Smith   | SATS1364    |     10         | Science 
 3258132 | Chris Smith   | ECTS4605    |      9         | Engineering

How would I go about creating the following

3258132  Chris Smith SATS1364, 10, Science + ECTS4605, 9,Engineering

where the + is just a new line. Notice how after the '+'(new line) it doesnt concat the id,name

you can use string_agg() function. check this http://stackoverflow.com/questions/12370083/concat-rows-in-postgres — Bhupesh, Apr 27 '17 at 06:59

score 0 · Answer 1 · answered Apr 27 '17 at 07:06

0

try

SELECT distinct concat(id,"name",string_agg(concat(subject, Lectured_Times , Faculty), chr(10))) 
from tn 
where id = 3258132 
group by id;

answered Apr 27 '17 at 07:06

Vao Tsun

47,234
13
100
132

score 0 · Answer 2 · answered Apr 28 '17 at 07:42

0

As mentioned above string_agg is perfect solution for this.

select 
    id, name, string_agg(concat(subject, Lectured_Times, Faculty), '\n')
from table
group by id, name

answered Apr 28 '17 at 07:42

Dmitry Chirkin

994
8
23

Concat multiple rows PSQL

2 Answers2