PostgreSQL create column for each array_agg element instead of comma-separate

Question

I want to force a new column for each string_agg element (i.e., Fiction, Mystery would instead be 'Fiction' in one column, 'Mystery' in the next column) returned from this query, and 2.) I need to be able to expand the tag-columns up to five tags max:

SELECT books.isbn_13 as "ISBN", title as "Title", 
  author as "Author", 
  string_agg(tag_name, ', ') as "Tags"
FROM books 
LEFT JOIN book_tags on books.isbn_13 = book_tags.isbn_13 
GROUP BY books.isbn_13;

Right now everything looks good, except I would like a column for each Tag instead of comma-separated values. Here is my CURRENT result:

    ISBN      |            Title            |      Author       |       Tags
1111111111111 | The Adventures of Steve     | Russell Barron    | Fiction, Mystery
2222222222222 | It's all a mystery to me    | Mystery Man       | Mystery
3333333333333 | Biography of a Programmer   | Solo Artist       | Biography
4444444444444 | Steve and Russel go to Mars | Russell Groupon   |
6666666666666 | Newest Book you Must Have   | Newbie Onthescene |

Desired result (separating tags into columns where there is more than one):

    ISBN      |            Title            |      Author       |       Tag1    | Tag2    | Tag3              | Tag4    
1111111111111 | The Adventures of Steve     | Russell Barron    | Fiction       | Mystery | Male Protagonists | Fantasy| 
2222222222222 | It's all a mystery to me    | Mystery Man       | Mystery
3333333333333 | Biography of a Programmer   | Solo Artist       | Biography
4444444444444 | Steve and Russel go to Mars | Russell Groupon   |
6666666666666 | Newest Book you Must Have   | Newbie Onthescene |

SCHEMA for books table (parent):
CREATE TABLE public.books
(
    isbn_13 character varying(13) COLLATE pg_catalog."default" NOT NULL,
    title character varying(100) COLLATE pg_catalog."default",
    author character varying(80) COLLATE pg_catalog."default",
    publish_date date,
    price numeric(6,2),
    content bytea,
    CONSTRAINT books_pkey PRIMARY KEY (isbn_13)
)

SCHEMA book_tags table:
CREATE TABLE public.book_tags
(
isbn_13 character varying(13) COLLATE pg_catalog."default" NOT NULL,
tag_name character varying(30) COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT book_tags_pkey PRIMARY KEY (isbn_13, tag_name),
CONSTRAINT book_tags_isbn_13_fkey FOREIGN KEY (isbn_13)
    REFERENCES public.books (isbn_13) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE CASCADE
)

I've researched group by, crosstab/pivot resources for hours with no luck. This seems like it should be a simple thing to do but I'm a very-beginner and haven't found an answer. Thanks in advance for any guidance.

**Show us db schema, sample data, current and expected output**. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. [**How to create a Minimal, Complete, and Verifiable example**](http://stackoverflow.com/help/mcve) Try create a sample in http://rextester.com — Juan Carlos Oropeza, Aug 07 '18 at 18:56
I couldn't get pivot to deal with multiple columns from my 'books' table, i.e. I could get first column 'ISBN' but the remaining columns (as far as I was able to tell) had to come from the Tags table. I would be thrilled to use pivot but couldn't get it to give me the first three (or four or books.* columns). I'll edit my question to include the schema for both tables. — bethm, Aug 07 '18 at 18:58
You probably need a dynamic pivot. Unless you have static number of tags. Also include the desire output, not sure what you like to get because you will have lot of nulls — Juan Carlos Oropeza, Aug 07 '18 at 18:59
Nulls are fine- The output I want is just as I described- this is a very small, very simple database. Currently only one book has two tags, so the two tags would be spearated in columns: — bethm, Aug 07 '18 at 19:05
again, post it so is clear. words can be interpreted differently. — Juan Carlos Oropeza, Aug 07 '18 at 19:06
`SCHEMA for books table (parent):` what `parent` do here? never use that statement — Juan Carlos Oropeza, Aug 07 '18 at 19:06
See I first thought you want a column named `'Fiction'`, any preference on the order? Can be more than two tags? — Juan Carlos Oropeza, Aug 07 '18 at 19:09
SQL doesn't have [dynamic output columns](https://stackoverflow.com/questions/15506199) so you'd better do this in your presentation layer. Otherwise the SQL statement with the Tag1...TagN columns must be generated in a 1st step, and executed in a 2nd step. — Daniel Vérité, Aug 07 '18 at 19:15
I had looked at that example, but it doesn't allow for multiple columns in the crosstab; only a 'header' column [in my case that would be the ISBN] with the tag1, tag2, tag3 etc after. I need all or most values from the books table and only the tags in separate columns from the book_tags table. That may not make sense at all. — bethm, Aug 07 '18 at 20:15
@JuanCarlosOropeza I edited my question (number of tags/tag columns expand up to five)- thank you for your patience with me as I was unclear- and did not want to cultter up stack overflow with a too-similar question that simply expands on this one. — bethm, Aug 08 '18 at 20:05

Juan Carlos Oropeza · Accepted Answer · 2018-08-09T15:19:42.977

1

With CTE as (    
    SELECT books.isbn_13 as "ISBN", 
           title as "Title", 
           author as "Author", 
           tag_name as "Tag",
           row_number() over (partition by books.isbn_13) as rn
    FROM books 
    LEFT JOIN book_tags 
      on books.isbn_13 = book_tags.isbn_13
)
SELECT "ISBN", "Title", "Author", 
       MAX( CASE WHEN rn = 1 THEN Tag END) as Tag1,
       MAX( CASE WHEN rn = 2 THEN Tag END) as Tag2,
       MAX( CASE WHEN rn = 3 THEN Tag END) as Tag3,
       MAX( CASE WHEN rn = 4 THEN Tag END) as Tag4,
       MAX( CASE WHEN rn = 5 THEN Tag END) as Tag5
FROM CTE
GROUP BY  "ISBN", "Title", "Author";

edited Aug 09 '18 at 15:19

answered Aug 07 '18 at 19:13

Juan Carlos Oropeza

47,252
12
78
118

This was close- isbn '111111111111' that had two tags (Fiction and Mystery) looked beautiful with tag1 column=Fiction and tag2 column=Mystery (rah..)- but the others, where they had a tag (as opposed to no tags) produced duplicate tag values, i.e. for ISBN '2222222222222' with only one tag, tag1= 'Mystery,' but tag2= 'Mystery' as well. – bethm Aug 07 '18 at 20:08
Can I use something like 'if exists' or equivalent do you think? – bethm Aug 07 '18 at 20:23
I edited my question and added a comment above. Thanks – bethm Aug 08 '18 at 20:10
Check again, with up to 5 tags – Juan Carlos Oropeza Aug 09 '18 at 15:20

PostgreSQL create column for each array_agg element instead of comma-separate

1 Answers1