String concatenation in SQL is frequently discussed on newsgroups and blogs.
Adam Machanic recently posted a competition in string concatenation
, which was followed by
Paul Nielsen's post
There were several suggestions on Connect to implement it, such as:
SQL needs version of MySQL group_Concat function
concatenate aggregation function
However, I decided to create my own suggestion, because I think that OLAP syntax is very explicit and clear, so let us stick to it. Also when you need concatenation, you need to specify a separator as well as an order in which to concatenate, which was not addressed in the previous suggestions.
Here is my suggestion.
There is one thing I do not have a strong opinion on: what should we do if one of the strings being concatenated contains a separator. For instance, if you requested a comma-separated list, and one of your strings already contains a comma. What should you do?