SQL - Split one record into two based on a single field-CodePudding

I have a single record with many fields one of the fields is unsubscribe_type. When the value of unsubscribe_type is 'Marketing and Reminder' I want to then create two rows for that record so basically two identical rows except now in unsubscribe_type one row will have 'Marketing' the other will have 'Reminder'

I have tried using a union all and two select queries but the main issue is running it as a CASE/IF statement that Im really struggling with.

Any help is useful not much code to look at currently as Ive tried various ways and no luck so far.

Example of Im looking to do.

Record in a table like this.

    No | Name | Address | unsubscribe_type 
    1 | Andrew | 123 St | Marketing and Reminder

Then I want to query that table and write out to another table two records like this:

    No | Name | Address | unsubscribe_type 
    1 | Andrew | 123 St | Marketing
    2 | Andrew | 123 St | Reminder

CodePudding user response：

Try this

Select no, name, address, itm
From your table, unnest( split (unsubscribe_type, "and")) as itm