| Double XML PATH | 3.1720 | 3.010 | 2,005 | 92,088 | 14,951 | 233,681 |įor each distinct id in #test it performs two operations instead of one but this operation is significantly cheaper than constructing the XML and then reparsing it.Īs already pointed out by Martin Smith, a CLR aggregate is probably your best bet. | | CPU Time (Seconds) | Elapsed Time (Seconds) | #test Scan Count | #test Logical Reads | Worktable logical reads | Worktable lob logical reads | I still found Kenneth's solution with two XML PATH calls much faster and less resource intensive. However for the following test data (1000 ids with 2156 rows per id for me) INSERT INTO #test The execution plan I get for the query in the question first does the concatenation for every row in the outer query and then removes the duplicates by id, SomeField_Combined1, SomeField_Combined2. I would materialise this into a #temp table first to ensure it is only evaluated once. You say that the source for this is an expensive query. But perhaps you don't want to use one for whatever reason. SELECT test.id,įOR XML PATH ('')),1,2,'') AS ConcatenatedSomeField,įOR XML PATH ('')),1,2,'') AS ConcatenatedSomeField2Ī CLR aggregate will almost certainly be the fastest way of doing this.
In some ways it seems like it should take longer but it doesn't. I ran this version which is the version I first learned. GROUP BY id, SomeField_Combined1, SomeField_Combined2 I ran a few tests using a little over 6 mil rows. The query is in an in-line TVF, so I cannot use a temporary table.Īlso, if there is a blank column the results will yield extra commasġ sam,ram,john, dodge,maserati,benz,mazda,GMC GROUP BY id,somefield_combined1,somefield_combined2Īre there any better solutions? The inner select comes from an expensive multi-table join (not the single table 'test' shown above). Using a solution I found on Stack Overflow: SELECT * FROM (
How to concatenate multiple columns in to a single row? For example: id name carġ sam,ram,john dodge,maserati,benz,mazda,GMC