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

Question

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'.

Answer 1

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