Concatinating rows in postgresql

Question

I have a many to many relationship in postgres that is books and authors. Authors can have many books and books can have many authors. I also have a join table that just contains the author_id and book_id. For simplicity's sake, just assume my authors table has an id, full_name and my books table has an id, title. Here is some sample data.

authors
id|full_name
------------
 1|Ben Hernandez
 2|Another Person

books
id|title
------------
 1|How to be terrible at SQL

book_author
book_id|author_id
-----------------
      1|1
      1|2

What I would like is a query that returns something like this

title                    |authors
---------------------------------
How to be terrible at SQL|Ben Hernandez,Another Person

Right now I am selecting title,author using joins on all three tables which is giving me this:

title                    |full_name
---------------------------------
How to be terrible at SQL|Ben Hernandez
How to be terrible at SQL|Another Person

and I am using javascript to build an array of authors with the results, which is working fine but if someone has a way to do this in sql, I would love to see it.

Thank you!

Ben Hernandez · Accepted Answer · 2016-03-28 20:45:22Z

3

Did some more googling and finally figured out this:

select books.title, string_agg(authors.full_name, ', ') from books
  inner join book_author on book_author.book_id = books.id
  inner join authors on book_author.author_id = authors.id
group by books.title;

answered Mar 28, 2016 at 20:45

Ben Hernandez

8773 gold badges11 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Concatinating rows in postgresql

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related