Converting multiple rows into a single comma separated row and vice versa

http://www.sqljason.com/2010/04/converting-multiple-rows-into-single.html

Recently, I have been bitten by the MSDN forums bug. I enjoy spending time in the SSAS and SSRS forums, and the learning that I take out from there is tremendous. Initially, I just used to read and learn, while now I reply to a lot of posts too (just the simple ones, keep the tough ones for the experts to solve ;) ).

So the other day, there was this post - how can you convert multiple rows into a single comma separated row? For eg,

This can be done by the FOR XML command.

select State, (
select City + ',' as [text()]
from tableA soi
where soi.State=t.State
order by City
for xml path( '' )
)
from tableA s )t

A handy command, especially because you don't have to use stored procedures to achieve the result

Converting a single comma separated row into multiple rows

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

 SELECT A.[State],  
     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 :)