How do you unflatten a delimited string column field to separate rows in SQL?

Question

How do you unflatten a delimited string column into separate rows? I couldn't find a good simple example in stackoverflow. Do I need to use the PIVOT function?

My example:

Table - Toys
ID - A01
SendNumber - '200, 203, 205’
Owner - Josh

Desired end Table
ID      SendNumber     Owner
A01         200                Josh
A01         203                Josh
A01         205                Josh

Thanks

Answer 1

You might get a better answer if you replace the generic term "SQL" with the specific product you're using.

There's no standard way to do this in SQL per se, you'd have to iterate over the table, parse each field, and issue INSERT statements for each value found. Specific SQL DBMSes may have proprietary methods to do what you want.

Answer 2

If it were me, I'd create the new table structure, and then write a stored procedure which uses a cursor and substring functions to loop through every row of the old table and insert new rows in the new table. When that's finished, you make sure all code/procedures point to the new table and then can delete the old table.

Answer 3

Is this what you mean?