In every DBA’s career I think having to concatenate results happens at least a few times. Probably more than we like to admit because we tend to live in table-land. :) However, there are those occasions, which are usually driven by some downstream requirement to format output. Now, I know that formatting should be handled by whatever data viewing method you are using, but sometimes that just isn’t possible or practical. Other times it may just be that we need to transform data from one system to another and that other system is not as normalized as the tables you are working with.
Like I said, I do it fairly infrequently, so I never remember the best way in my head. I usually end up looking at how I’ve done it in the past. I started thinking that there may be better ways then some of the convoluted strategies I’ve found in previous solutions.
Trusty Google sent me here:
http://www.projectdmx.com/tsql/rowconcatenate.aspx
It’s an incredible (though certainly not exhaustive) list of ways to deal with this depending on your need. I like XML and chose to go with simplicity so, for this particular task, I went with Eugene Kogan’s “blackbox XML” method. It’s only a few lines and if you are familiar with XML and SQL then it’s not that hard to understand.
I’ve definitely bookmarked this for later reference!