Optimal way to concatenate/aggregate strings

Question

I'm finding a way to aggregate strings from different rows into a single row. I'm looking to do this in many different places, so having a function to facilitate this would be nice. I've tried solutions using COALESCE and FOR XML, but they just don't cut it for me.

String aggregation would do something like this:

id | Name                    Result: id | Names

-- - ----                            -- - -----

1  | Matt                            1  | Matt, Rocks

1  | Rocks                           2  | Stylus

2  | Stylus

I've taken a look at CLR-defined aggregate functions as a replacement for COALESCE and FOR XML, but apparently SQL Azure does not support CLR-defined stuff, which is a pain for me because I know being able to use it would solve a whole lot of problems for me.

Is there any possible workaround, or similarly optimal method (which might not be as optimal as CLR, but hey I'll take what I can get) that I can use to aggregate my stuff?

Answer 1

I have created a table named details. 

You can try the following:

SELECT ID, STRING_AGG(Name, ',') AS okay FROM details

GROUP BY ID;

SELECT STRING_AGG(Name, ',') Name1 from details group by ID;

SELECT Main.ID,

       LEFT(Main.Name,Len(Main.Name)-1) As "names"

FROM

    (

        SELECT DISTINCT ST2.ID, 

            (

                SELECT ST1.Name + ',' AS [text()]

                FROM dbo.details ST1

                WHERE ST1.ID = ST2.ID

                ORDER BY ST1.ID

                FOR XML PATH ('')

            ) [Name]

        FROM dbo.details ST2

    ) [Main]