1

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?

  • Very bad data model. You should have a book table and a chapter table with a FOREIGN KEY between chapter and book (child->parent). – Vérace Nov 1 '17 at 22:11
0

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.