0

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.

kjsr7
  • 397
  • 4
  • 20
Ahmad Ali
  • 45
  • 9
  • This sort of operation is probably not best suited to be handled in SQL Server. Is there any chance you could handle this requirement outside of your database? – Tim Biegeleisen Apr 30 '20 at 17:00
  • Does this answer your question? [Turning a Comma Separated string into individual rows](https://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows) – kjsr7 Apr 30 '20 at 17:05
  • Someone just asked this for Oracle. Small world. Versions of Oracle older than 12c also didn't have this functionality, but folks often resort to recursive CTE's to solve this inside the database [like this question for sql server](https://stackoverflow.com/questions/6381587/efficient-way-to-string-split-using-cte). – JNevill Apr 30 '20 at 17:06
  • @TimBiegeleisen we also use Pentaho Data Integration. Is there a way to do this through it? – Ahmad Ali Apr 30 '20 at 17:24

0 Answers0