Ever since I wrote Converting multiple rows into a single comma separated row, I was trying to find a SQL command which will do the reverse, which is converting the single comma separated row back to multiple rows. I checked up a few blogs and I found out that it was possible to do with the help of custom functions or stored procedures, but I was not interested in all of them. Finally I got the answer that I was looking for, and now that I did, I did not waste precious time in noting it down here, lest I forget.
Let me try to show you what we are trying to achieve here with the help of an image
This can be done with the help of the below query
Split.a.value('.', 'VARCHAR(100)') AS String
FROM (SELECT [State],
CAST ('<M>' + REPLACE([City], ',', '</M><M>') + '</M>' AS XML) AS String
FROM TableA) AS A CROSS APPLY String.nodes ('/M') AS Split(a);
Again, no stored procedures or function, just plain old SQL :)
Update (23/05/2010) : I saw this blog by Brad Schulz (MVP) explaining this scenario and the performance implications in detail. So people who are serious about getting this in their code, please read his blog before you do so - Delimited String Tennis Anyone?