T-SQL有没有用于连接字符串的聚合函数？

Question

T-SQL有没有用于连接字符串的聚合函数？

sql-serversql-server-2005t-sqlgroup-byaggregate-functions

85

我有一个视图，查询它的样子像这样：

可能是重复问题：
在SQL Server 2000中使用implode类型函数？
连接行值T-SQL

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

我需要编写一个查询，将BuildingNames分组并显示类似于以下内容的PollNumbers列表：

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

我该如何在 T-SQL 中实现这个功能？我不想写一个存储过程，因为那似乎有些大材小用，但我并不是一个数据库专家。看起来像 SUM() 或 AVG() 这样的聚合函数是我需要的，但我不确定T-SQL是否有这样的函数。我使用的是 SQL Server 2005。

- Brant Bobby

是的，这个问题在 SO 上被问了多次。https://dev59.com/KXI-5IYBdhLWcg3wZ3cR 或 http://stackoverflow.com/questions/3121079/how-to-concatenate-multiple-rows - Lamak

啊，我的错。这个问题可以归结为没有使用正确的搜索关键字。 :) 我投票关闭。 - Brant Bobby

已经回答了很多次...但是要注意，不是所有的FOR XML PATH concatenations实现都能像我的示例代码（如下所示）一样正确处理XML特殊字符（<、&、>等）。 - KM.

也是一个重复的问题：https://dev59.com/I3VC5IYBdhLWcg3wfxU8 - Danny Varod

5

SqlServer 2017现在有了STRING_AGG，它可以使用指定的分隔符将多个字符串聚合成一个字符串。 - John

2个回答

36

Sql Server中没有内置函数来实现此功能，但可以通过编写用户定义的聚合函数来实现。这篇文章提到了一个函数，作为SQL Server样例的一部分：http://msdn.microsoft.com/en-us/library/ms182741.aspx

下面是Concatenate聚合函数的示例代码。要使用它，在Visual Studio中创建一个数据库项目，添加新的SqlAggregate并将代码替换为下面的示例。部署后，您应该在数据库中找到一个新的程序集和一个名为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());
    }
}

要使用它，您只需编写一个聚合查询：

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

查询的输出结果为：

-- Output
-- ===================
-- This is just a test
-- ,
-- do not be alarmed , this is just a test

我将这个类和集合打包成了一个脚本，您可以在这里找到：https://gist.github.com/FilipDeVos/5b7b4addea1812067b09

- Filip De Vos

11

+1这对我比被选中的答案更有帮助。正是我所需要的。 - Isaac Fife

1

@SerjSagan 我添加了代码、示例和安装脚本链接，这样阅读此回答的人就更清楚了。 - Filip De Vos

2

通过这种方法，我如何控制被聚合的项目的顺序？似乎顺序将由聚集索引确定。 - Mike

2

这是为您的产品增加可维护性屏障的绝佳方式。 - marknuzz

1

为什么？无论如何，您都不应直接在服务器上编辑任何内容，而构建/安装程序也不关心它是否正在安装SQL脚本或二进制文件。 - Filip De Vos

显示剩余8条评论

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- KM. · Accepted Answer

使用 SQL Server 2017 及以上版本，请使用：

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

输出：

HeaderValue 
----------- -------------
1           CCC
2           B<&>B, AAA
3           <br>, A & Z

(3 rows affected)

对于 SQL Server 2005 至 2016，您需要执行类似以下操作：

--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

输出：

HeaderValue ChildValues
----------- -------------------
1           CCC
2           AAA, B<&>B
3           <br>, A & Z

(3 row(s) affected)

还要注意的是，并非所有的FOR XML PATH连接都能正确地处理XML特殊字符，例如上面的示例。