Filter one column by value in second column postgres

Question

I'm constructing a large table that is pulling values from across the database. Two of the fields I want to include in the new table are stored in a single column in another table. I want to split this field in the current table into two columns in my new table. Ideally, I would do this as part of a larger select statement but otherwise, I can use a CTE or temp table.

For example, the data is stored like this:

CREATE TABLE foo(id,title,type)
AS VALUES 
 ( 1, 'Book Title 1', 'Book' ),
 ( 2, 'Book Title 2', 'Book' ),
 ( 1, 'Chapter 1', 'Chapter' ),
 ( 1, 'Chapter 2', 'Chapter' ),
 ( 2, 'Chapter 1', 'Chapter' );

And I'd want my new table to be like this:

|Book | Chapter |
--------------------------------
| Book Title 1 | Chapter 1 |
| Book Title 1 | Chapter 2 |
| Book Title 2 | Chapter 1 |

How do I write a SQL query that can perform this transformation?

Can this type of transformation be done as part of a larger SQL query or should I create the table and then join it to my other tables?

Or should I export the data and reshape it with R and the load it back into the DB?

• Vérace: Very bad data model. You should have a book table and a chapter table with a FOREIGN KEY between chapter and book (child->parent).

Answer 1

I think you want a self join like this,

SELECT f1.title AS book, f2.title AS subtitle
FROM foo AS f1
JOIN foo AS f2
 USING (id)
WHERE f1.title <> f2.title
 AND f1.title LIKE 'Book%'
ORDER BY 1, 2;
 title | title 
--------------+-----------
 Book Title 1 | Chapter 1
 Book Title 1 | Chapter 2
 Book Title 2 | Chapter 1
(3 rows)

If all subtitles are chapters, you can also do this

SELECT f1.title AS book, f2.title AS chapter
FROM foo AS f1
JOIN foo AS f2
 USING (id)
WHERE f1.title <> f2.title
 AND f1.title LIKE 'Book%'
 AND f2.title LIKE 'Chapter%'
ORDER BY 1, 2;

Answer 2

You can select the Books and Chapters separatly and then join them back together on the id.

SELECT b.title book_title, c.title chapter_title
FROM 
 (SELECT * FROM foo WHERE type='Book')b
 INNER JOIN 
 (SELECT * FROM foo WHERE type='Chapter')c 
 ON b.id=c.id