Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table collections (
collection_id integer,
added_on timestamp ,
edited_on text  ) ;

insert into collections values 
(1,'2024-06-19 20:16:41',null),
(2,'2024-06-19 20:17:42',null),
(3,'2024-06-19 20:17:57',null);


create table collection_details  (
detail_id integer,
collection integer,
detail_type text,
detail_value text  ) ;

insert into collection_details  values 
(1,1,'title','Book title 1'),
(2,1,'author','John Doe'),
(3,1,'publisher','Publisher A'),
(4,2,'title','Book title 2'),
(5,2,'author','Jane Doe'),
(6,3,'title','Book title 3'),
(7,3,'author','John Doe'),
(8,3,'publisher','Publisher B');

CREATE TABLE



INSERT 0 3



CREATE TABLE



INSERT 0 8


select * from collections;
select * from collection_details;


collection_id
added_on
edited_on


1
2024-06-19 20:16:41
null

2
2024-06-19 20:17:42
null

3
2024-06-19 20:17:57
null



SELECT 3




detail_id
collection
detail_type
detail_value


1
1
title
Book title 1

2
1
author
John Doe

3
1
publisher
Publisher A

4
2
title
Book title 2

5
2
author
Jane Doe

6
3
title
Book title 3

7
3
author
John Doe

8
3
publisher
Publisher B



SELECT 8


SELECT  c.collection_id,
        MAX(cdTitle.detail_value) FILTER (WHERE cdTitle.detail_type = 'title') AS title,
        MAX(cdAuth.detail_value) FILTER (WHERE cdAuth.detail_type = 'author') AS author
FROM collections c
LEFT JOIN collection_details cdTitle ON c.collection_id = cdTitle.collection AND cdTitle.detail_type = 'title'
LEFT JOIN collection_details cdAuth ON c.collection_id = cdAuth.collection AND cdAuth.detail_type = 'author'
GROUP BY c.collection_id
ORDER BY c.collection_id asc;


collection_id
title
author


1
Book title 1
John Doe

2
Book title 2
Jane Doe

3
Book title 3
John Doe



SELECT 3


SELECT collection_id, book_author,book_title
FROM collections c
LEFT JOIN  (SELECT collection,
                  MAX(CASE WHEN detail_type = 'author' THEN detail_value END) AS book_author ,
                  MAX(CASE WHEN detail_type = 'title' THEN detail_value END) AS book_title 
           FROM  collection_details 
           GROUP BY collection 
         ) cd on cd.collection=c.collection_id
ORDER BY collection_id asc;


collection_id
book_author
book_title


1
John Doe
Book title 1

2
Jane Doe
Book title 2

3
John Doe
Book title 3



SELECT 3

collection_id	added_on	edited_on
1	2024-06-19 20:16:41	null
2	2024-06-19 20:17:42	null
3	2024-06-19 20:17:57	null

detail_id	collection	detail_type	detail_value
1	1	title	Book title 1
2	1	author	John Doe
3	1	publisher	Publisher A
4	2	title	Book title 2
5	2	author	Jane Doe
6	3	title	Book title 3
7	3	author	John Doe
8	3	publisher	Publisher B