Aggregation Of Strings In T-SQL

Suppose, you have a scenario where you need to group by some column and in the mean time aggregate (concatenation of string in our case) "strings"... how can we do that?

Well, there are numerous methods, Anith, has given a detailed method about them which could be found in his blog at http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/.

However, the one, which I liked was by using FOR XML PATH.

Let's see the demonstration -

1. Create a table where we have two columns A (for grouping) and B (for concatenation of strings) and then insert some dummy values -

DECLARE @table Table(A INT, B VARCHAR)
INSERT INTO @table VALUES(1,'A') ,(1,'B'),(1,'C'),(2,'X'),(2,'Y'),(2,'Z')
 
2. Now type following query, which will achieve the desired result -
 
SELECT T1.A,( SELECT B + ',' FROM @table T2 WHERE T2.A = T1.A FOR XML PATH('')) AS B
FROM @table T1
GROUP BY A ;
 
and the desired output is -
 
A          B
1        A,B,C
2        X,Y,Z
 
FOR XML PATH is used to generate XML code from the SQL.
 
This solution is tricky but elegant!