Join and Concatenate rows from table into into string

Question

I have 2 tables consider table named as fp and batch, I have to join 2 tables based on fp[primary key] of 1st table and fp_inst_id from 2nd table such that my output is :

First table all columns and 2nd table one column which is concatenated string of all the rows from join of table 1 and table 2 on fp.id and batch.fp_inst_id. Note : [there will be multiple fp_inst_id(of table 2) for unique ID(of table 1)]

Let me give you an example :

Created tables :

CREATE TABLE fp (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255) 
);

CREATE TABLE batch (
    batchID int,
    fp_inst_id int,
    xyz varchar(255),
    abc varchar(255) 
);

insert into fp values(1,'savan','nahar','abc','xyz');
insert into fp values(2,'mmm','asmd','aawd','12k3mn');

insert into batch values(1,1,'garbage1', 'abc1');
insert into batch values(2,1,'garbage2', 'abc2');
insert into batch values(3,1,'garbage3', 'abc3');
insert into batch values(4,2,'garbage9', 'abc9');

If i do normal join like this :

select * from fp join batch on fp.PersonID = batch.fp_inst_id;

What I want is :

Batch columns can be different like it's ok if it has some other delimiter of not surrounded by [] and separated on ';' or something.

What I have tried:

The same thing can be done using MYSQL using STUFF, FOR XML PATH

But it seems to be difficult in POSTGRES SQL as it doesn't support these things, In POSTGRES SQL I tried string_agg, but it says me to group by everything

2nd thing I was trying was : Using with clause first create the concatenated strings of table 2 group by on fp_inst_id, but in POSTGRES SQL, it allows group by on primary key(which is normal select) or it asks to use the aggregate function

I'm trying to do this in POSTGRES SQL through a query.

Thanks for the help in advance

Jasen · Accepted Answer · 2019-06-05 05:20:06Z

Use array_agg to combine the batch rows and group-by to bracket the combination.

select personid,lastname,firstname,address,city,
     array_agg(batch) 
from fp 
join batch on fp.PersonID = batch.fp_inst_id 
group by personid,lastname,firstname,address,city;

eg:

jasen=# select personid,lastname,firstname,address,city,array_agg(batch) from fp join batch on fp.PersonID = batch.fp_inst_id group by 1,2,3,4,5;

 personid | lastname | firstname | address |  city  |                              array_agg                              
----------+----------+-----------+---------+--------+---------------------------------------------------------------------
        2 | mmm      | asmd      | aawd    | 12k3mn | {"(4,2,garbage9,abc9)"}
        1 | savan    | nahar     | abc     | xyz    | {"(1,1,garbage1,abc1)","(2,1,garbage2,abc2)","(3,1,garbage3,abc3)"}

here the batch column technically contains an array of tuples, but the sting representation seems acceptable.

Thanks, it worked and I learnt about array_agg, while using array_agg in API it causes few issues, so i just converted into string_agg ! Thanks a lot

krithikaGopalakrishnan · Accepted Answer · 2019-06-05 05:12:48Z

Alternatively you can use concat_ws() to concat the values and then group by

select personid,lastname,firstname, address,city, array_agg(batch_columns) as batch_columns 
from 
(select fp.*, concat_ws(' / ',batch.batchid,batch.fp_inst_id, batch.xyz,batch.abc)::text as batch_columns 
from fp 
join batch 
on fp.personid=batch.fp_inst_id)as table1
group by 1,2,3,4,5;


personid  | lastname | firstname | address |  city  |                                  batch_columns                                  
----------+----------+-----------+---------+--------+---------------------------------------------------------------------------------
        1 | savan    | nahar     | abc     | xyz    | {"1 / 1 / garbage1 / abc1","2 / 1 / garbage2 / abc2","3 / 1 / garbage3 / abc3"}
        2 | mmm      | asmd      | aawd    | 12k3mn | {"4 / 2 / garbage9 / abc9"}

Collectives™ on Stack Overflow

Join and Concatenate rows from table into into string

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related