Concat groups in SQL Server [duplicate]

Question

If I have a table like this:

+------------+
| Id | Value |
+------------+
| 1  | 'A'   |
|------------|
| 1  | 'B'   |
|------------|
| 2  | 'C'   |
+------------+

How can I get a resultset like this:

+------------+
| Id | Value |
+------------+
| 1  | 'AB'  |
|------------|
| 2  | 'C'   |
+------------+

I know this is really easy to do in MySQL using GROUP_CONCAT, but I need to be able to do it in MSSQL 2005

Thanks

(Duplicate of How to use GROUP BY to concatenate strings in SQL Server?)

Answer 1

For a clean and efficient solution you can create an user defined aggregate function, there is even an example that does just what you need.
You can then use it like any other aggregate function (with a standard query plan):