Postgresql – Filter one column by value in second column postgres

postgresqlself-join

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?

Best Answer

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;
Related Question