CODEWITHSUNDEEP
postgresql
Gerd
Gerd

Reputation: 2603

How can I get a data list as string in Postgres SQL query?

My tables are

create table parents(
id integer,
name text,
parent_childs text);

create table childs(
parent_id integer,
name text);

Data:

insert into parents values (1, 'Mueller');

insert into childs values (1, 'Peter');

insert into childs values (1, 'Hans');

How can I select this data in format:

parent_name    childs
------------------------
Mueller        Peter, Hans
Bayer          Anna, Petra, Max

And I looking for a simple update to write all names from childs from one parents entry in column parents.parent_childs like 'Peter, Hans'.

Upvotes: 0

Views: 886

Answers (1)

Vivek S.
Vivek S.

Reputation: 21935

To update :

string_agg(expression, delimiter) - input values concatenated into a string, separated by delimiter

In your case :-

  • expression :- childs.name
  • delimiter :-,
update parents 
set parent_childs =t.cname 
    from(
         select parent_id
               ,string_agg(childs.name,',') cname 
         from childs group by parent_id
        )t
where t.parent_id=parents.id

How can I select this data in format:

parent_name    childs
------------------------
Mueller        Peter, Hans
Bayer          Anna, Petra, Max

Try this : 

select parents.name as parent_name
      ,string_agg(childs.name,',') childs 
from childs 
inner join  parents on childs.parent_id=parents.id 
group by parent_id,parents.name

Upvotes: 2

Related Questions