I have a simple set of tables used for a many-to-many relationship - a book can have many authors, an author can have many books:
CREATE TABLE book (
book_id serial PRIMARY KEY, -- implicit primary key constraint
book_name text NOT NULL
);
CREATE TABLE author (
author_id serial PRIMARY KEY, -- implicit primary key constraint
author_name text NOT NULL
);
CREATE TABLE book_author (
book_author_id serial PRIMARY KEY,
book_id int,
author_id numeric NOT NULL
);
I can query for a particular book to get all the authors:
SELECT
author_name
from
author a
where
a.book_id = 23
But there could be any number of rows returned, because there could be any number of authors for the book.
What I want is a way to format the result (no matter how many rows) into a nicely formatted string (like "Robert Heinlein, Arthur C. Clarke, Ray Bradbury").
Is this possible in postgreSQL?