When would I use this:
In cases when you have a table containing an identifier field with a related field containing delimited text and you need to split into a new row per split subfield.
Example:
The Email Address column contains email addresses delimited by commas. We'd like to transform the upper table into the lower table where each email address is its own row related to its payroll.
Using the sample data from above:
CREATE TABLE Testdata(PayrollId int, Country VARCHAR(2), EmailAddress varchar(max))INSERT Testdata select 1, 'GB', 'email1,email2,email3'INSERT Testdata select 2, 'GB', 'email1,email2'We create a temporary table to hold the results of the recursion:
;WITH tmp(PayrollId, Country, EmailAd, EmailAddress) as( SELECT PayrollId, Country, LEFT(EmailAddress, CHARINDEX(',',EmailAddress+',')-1), STUFF(EmailAddress,1,CHARINDEX(',',EmailAddress+','),'') FROM Testdata UNION ALL
SELECT PayrollId, Country, LEFT(EmailAddress, CHARINDEX(',',EmailAddress+',')-1), STUFF(EmailAddress,1,CHARINDEX(',',EmailAddress+','),'') FROM tmp WHERE EmailAddress > '')Note: Tested on SQL Server.
