0

I want to join a table which has multiple rows and need to merge one of the column from all the rows into single column. 

select a.parent_id,a.parent_name,concat(b.child_name) from parent a 
join children b on (a.parent_id=b.parent_id);

This should return all the parent rows and each parent row should have all its children's.

i am thinking to group with parent_id but getting multiple records (one record per child). What logic i can implement here apart from grouping to get all child's for a parent in single row.

janasainik
  • 811
  • 5
  • 20
  • 40
  • Possible duplicate of [How to concatenate strings of a string field in a PostgreSQL 'group by' query?](http://stackoverflow.com/questions/43870/how-to-concatenate-strings-of-a-string-field-in-a-postgresql-group-by-query) – Matt Aug 26 '16 at 22:55
  • postgres 9.0 + string_agg() and group by – Matt Aug 26 '16 at 22:55
  • If i do group by with child_name, i am getting error as - column "a.parent_name" must appear in the GROUP BY clause or be used in an aggregate function – janasainik Aug 26 '16 at 23:01
  • If i add parent_id,parent_name and child_name in group by clause, i am getting one row for each child. – janasainik Aug 26 '16 at 23:07
  • 1
    child_name shouldn't go in the group by clause. It is what you want to aggregate. See my answer below. – Matt Aug 26 '16 at 23:08
  • Got it now, child_name should not be present in group by. – janasainik Aug 26 '16 at 23:13

1 Answers1

5
SELECT a.parent_id, a.parent_name, STRING_AGG(b.child_name, ',') as Children
FROM
    Parent a
    INNER JOIN children b
    ON a.Id = b.ParentId
GROUP BY
    a.parent_id
    ,a.parent_name
Matt
  • 13,833
  • 2
  • 16
  • 28