If you're allowed to use CLR in your environment, this is a tailor-made case for a user-defined aggregate.
In particular, this is probably the way to go if the source data is non-trivially large and/or you need to do this type of thing a lot in your application. I strongly suspect the query plan for Aaron's solution will not scale well as the input size grows. (I tried adding an index to the temp table, but that didn't help.)
This solution, like many other things, is a tradeoff:
- Politics/policy for even using CLR Integration in your, or your client's, environment.
- CLR function is likely faster, and will scale better given a real set of data.
- CLR function will be reusable in other queries, and you won't have to duplicate (and debug) a complex subquery every time you need to do this type of thing.
- Straight T-SQL is simpler than writing and managing a piece of external code.
- Perhaps you don't know how to program in C# or VB.
EDIT: Well, I went to try to see if this actually was better, and it turns out the requirement that the comments be in a specific order is currently not possible to satisfy using an aggregate function. :(
See SqlUserDefinedAggregateAttribute.IsInvariantToOrder. Basically, what you need to do is
OVER(PARTITION BY customer_code ORDER BY row_num) but
ORDER BY is not supported in the
OVER clause when aggregating. I'm assuming adding this functionality to SQL Server opens a can of worms, because what would need to be changed in the execution plan is trivial. The aforementioned link says this is reserved for future use, so this could be implemented in the future (on 2005 you're probably out of luck, though).
This could still be accomplished by packing and parsing the
row_num value into the aggregated string, and then doing the sort within the CLR object... which seems pretty hackish.
In any event, below is the code I used in case anyone else finds this useful even with the limitation. I'll leave the hacking part as an exercise for the reader. Note that I used AdventureWorks (2005) for test data.
IsNullIfEmpty = false,
IsInvariantToDuplicates = false,
IsInvariantToNulls = true,
IsInvariantToOrder = false,
MaxByteSize = -1
public class StringConcatAggregate : IBinarySerialize
private string _accum;
private bool _isEmpty;
public void Init()
_accum = string.Empty;
_isEmpty = true;
public void Accumulate(SqlString value)
_accum += ' ';
_isEmpty = false;
_accum += value.Value;
public void Merge(StringConcatAggregate value)
public SqlString Terminate()
return new SqlString(_accum);
public void Read(BinaryReader r)
_accum = r.ReadString();
_isEmpty = _accum.Length == 0;
public void Write(BinaryWriter w)
T-SQL for testing (
CREATE ASSEMBLY, and
sp_configure to enable CLR omitted):
CREATE TABLE [dbo].[Comments]
CustomerCode int NOT NULL,
RowNum int NOT NULL,
Comments nvarchar(25) NOT NULL
INSERT INTO [dbo].[Comments](CustomerCode, RowNum, Comments)
DENSE_RANK() OVER(ORDER BY FirstName),
ROW_NUMBER() OVER(PARTITION BY FirstName ORDER BY ContactID),
CREATE AGGREGATE [dbo].[StringConcatAggregate]
EXTERNAL NAME StringConcatAggregate.[MyCompany.SqlServer.StringConcatAggregate]
[dbo].[StringConcatAggregate](Comments) AS AllComments
GROUP BY CustomerCode