T-SQL 是否有连接字符串的聚合函数？

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 test
group by class


drop 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

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.