There are probably a ton of uses for combining
I'll start off with a snippet of a larger script. This snippet concatenates the index columns into a single field.
asked Apr 14 '10 at 08:54 PM in Default
Reporting is the obvious answer. I also use it for providing a dynamic pivot. Actually, I use it remarkably regularly, and don't really think about the specific cases.
But actually, you should change the way you use it. You'll notice that if you have an ampersand (or > or <) in your string, that this gets "HTMLEncoded" into
The way to get around this is to do it slightly differently. Like this:
....which I've blogged about at http://sqlblog.com/blogs/rob_farley/archive/2010/04/15/handling-special-characters-with-for-xml-path.aspx
I think the vast majority of the use cases will be for string contatentation very similar to what you have done. I have frequently used it to turn attributes stored in a many-to-one relationship with the entity into a single string for display or use in reports. I frequently turn those things into views to make them easier to query while still maintaining the base data in a normalized form.
I won't provide a code snippet, because Jeff Moden has already done it better than I could. His article at
answered Apr 14 '10 at 09:14 PM