Does T-SQL have an aggregate function to concatenate strings? [duplicate]
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 offSELECT 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.HeaderValueSELECT HeaderValue, STRING_AGG(ChildValue,', ') FROM @YourTable GROUP BY HeaderValue
OUTPUT:
HeaderValue ----------- -------------1 CCC2 B<&>B, AAA3 <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 offSELECT 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 CCC2 AAA, B<&>B3 <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.
There is no built in function in Sql Server, but it can be achieved by writing a user defined aggregate. This article mentions such a function as part of the SQL Server samples: http://msdn.microsoft.com/en-us/library/ms182741.aspx
As an example I include the code for a Concatenate aggregate. To use it, create a database project in Visual Studio, add new SqlAggregate and replace the code with the sample below. Once deployed you should find a new assembly in your database and an aggregate function Concatenate
using System;using System.Data.SqlTypes;using System.IO;using System.Text;using Microsoft.SqlServer.Server;[Serializable][SqlUserDefinedAggregate(Format.UserDefined, IsInvariantToNulls = true, IsInvariantToDuplicates = false, IsInvariantToOrder = false, MaxByteSize = 8000, Name = "Concatenate")]public class Concatenate : IBinarySerialize{ private StringBuilder _intermediateResult; internal string IntermediateResult { get { return _intermediateResult.ToString(); } } public void Init() { _intermediateResult = new StringBuilder(); } public void Accumulate(SqlString value) { if (value.IsNull) return; _intermediateResult.Append(value.Value); } public void Merge(Concatenate other) { if (null == other) return; _intermediateResult.Append(other._intermediateResult); } public SqlString Terminate() { var output = string.Empty; if (_intermediateResult != null && _intermediateResult.Length > 0) output = _intermediateResult.ToString(0, _intermediateResult.Length - 1); return new SqlString(output); } public void Read(BinaryReader reader) { if (reader == null) throw new ArgumentNullException("reader"); _intermediateResult = new StringBuilder(reader.ReadString()); } public void Write(BinaryWriter writer) { if (writer == null) throw new ArgumentNullException("writer"); writer.Write(_intermediateResult.ToString()); }}
To use it, you can simply write an aggregate query:
create table test( id int identity(1,1) not null primary key, class tinyint not null, name nvarchar(120) not null )insert into test values (1, N'This'),(1, N'is'),(1, N'just'),(1, N'a'),(1, N'test'),(2, N','),(3, N'do'),(3, N'not'),(3, N'be'),(3, N'alarmed'),(3, N','),(3, N'this'),(3, N'is'),(3, N'just'),(3, N'a'),(3, N'test')select dbo.Concatenate(name + ' ')from testgroup by classdrop table test
The output of the query is:
-- Output-- ===================-- This is just a test-- ,-- do not be alarmed , this is just a test
I packaged up the class and the aggregate as a script which you can find here: https://gist.github.com/FilipDeVos/5b7b4addea1812067b09