I have a table with the following structure:
id status column1 column2
----------------------------------------
1 active apple,pear bear,dog
2 active apple cat,rabbit
3 paused orange,lemon cat
4 active lemon dog
I need to be able to split each substring into a new row, but still maintaining all the existing combinations between column 1 and 2.
So the desired output should be something like this:
id status column1 column2
--------------------------------------
1 active apple bear
1 active apple dog
1 active pear bear
1 active pear dog
2 active apple cat
2 active apple rabbit
3 paused orange cat
3 paused lemon cat
4 active lemon dog
The actual table I need to apply this to has 13 columns, with 6 of them having multiple substings, and each of these 6 columns having up to 10 substrings.
I'm using SQL Server 2008, so I can't use STRING_SPLIT.
