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 And here is a great place to START to learn how improve your question quality and get better answers. How to create a Minimal, Complete, and Verifiable example Try create a sample in rextester.com — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Aug 7, 2018 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
– bethm, Commented Aug 7, 2018 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
– Juan Carlos Oropeza, Commented Aug 7, 2018 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
– bethm, Commented Aug 7, 2018 at 19:05

Juan Carlos Oropeza · Accepted Answer · 2018-08-09 15:19:42Z

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 9, 2018 at 15:19

answered Aug 7, 2018 at 19:13

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

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

4 Comments

bethm Over a year ago

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 Over a year ago

Can I use something like 'if exists' or equivalent do you think?

bethm Over a year ago

I edited my question and added a comment above. Thanks

Juan Carlos Oropeza Over a year ago

Check again, with up to 5 tags

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related