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?