CODEWITHSUNDEEP
postgresqlpsqlstring-aggregation
bethm
bethm

Reputation: 1

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

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.

Upvotes: 0

Views: 306

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

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";

Upvotes: 1

Related Questions