Does T-SQL have an aggregate function to concatenate strings? [duplicate]

Question

Possible Duplicates:
Implode type function in SQL Server 2000?
Concatenate row values T-SQL

I have a view which I'm querying that looks like this:

BuildingName    PollNumber ------------    ---------- Foo Centre      12         Foo Centre      13 Foo Centre      14 Bar Hall        15 Bar Hall        16 Baz School      17 

I need to write a query that groups BuildingNames together and displays a list of PollNumbers like this:

BuildingName    PollNumbers ------------    ----------- Foo Centre      12, 13, 14 Bar Hall        15, 16 Baz School      17 

How can I do this in T-SQL? I'd rather not resort to writing a stored procedure for this, since it seems like overkill, but I'm not exactly a database person. It seems like an aggregate function like SUM() or AVG() is what I need, but I don't know if T-SQL has one. I'm using SQL Server 2005.

Answer 1

for SQL Server 2017 and up use:

STRING_AGG()

set nocount on; declare @YourTable table (RowID int, HeaderValue int, ChildValue varchar(5)) insert into @YourTable VALUES (1,1,'CCC') insert into @YourTable VALUES (2,2,'B<&>B') insert into @YourTable VALUES (3,2,'AAA') insert into @YourTable VALUES (4,3,'<br>') insert into @YourTable VALUES (5,3,'A & Z') set nocount off SELECT     t1.HeaderValue         ,STUFF(                    (SELECT                         ', ' + t2.ChildValue                         FROM @YourTable t2                         WHERE t1.HeaderValue=t2.HeaderValue                         ORDER BY t2.ChildValue                         FOR XML PATH(''), TYPE                    ).value('.','varchar(max)')                    ,1,2, ''               ) AS ChildValues     FROM @YourTable t1     GROUP BY t1.HeaderValue  SELECT     HeaderValue, STRING_AGG(ChildValue,', ')     FROM @YourTable     GROUP BY HeaderValue 

OUTPUT:

HeaderValue  ----------- ------------- 1           CCC 2           B<&>B, AAA 3           <br>, A & Z  (3 rows affected) 

for SQL Server 2005 and up to 2016, you need to do something like this:

--Concatenation with FOR XML and eleminating control/encoded character expansion "& < >" set nocount on; declare @YourTable table (RowID int, HeaderValue int, ChildValue varchar(5)) insert into @YourTable VALUES (1,1,'CCC') insert into @YourTable VALUES (2,2,'B<&>B') insert into @YourTable VALUES (3,2,'AAA') insert into @YourTable VALUES (4,3,'<br>') insert into @YourTable VALUES (5,3,'A & Z') set nocount off SELECT     t1.HeaderValue         ,STUFF(                    (SELECT                         ', ' + t2.ChildValue                         FROM @YourTable t2                         WHERE t1.HeaderValue=t2.HeaderValue                         ORDER BY t2.ChildValue                         FOR XML PATH(''), TYPE                    ).value('.','varchar(max)')                    ,1,2, ''               ) AS ChildValues     FROM @YourTable t1     GROUP BY t1.HeaderValue 

OUTPUT:

HeaderValue ChildValues ----------- ------------------- 1           CCC 2           AAA, B<&>B 3           <br>, A & Z  (3 row(s) affected) 

Also, watch out, not all FOR XML PATH concatenations will properly handle XML special characters like my above example will.