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
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.
