T-SQL是否有一个聚合函数来连接字符串?(复制)

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

可能的重复:内爆类型函数在SQL Server 2000?连接行值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.

如何在T-SQL中实现这一点?我不想为此编写一个存储过程，因为这似乎有些过分，但我并不是一个真正的数据库人员。看起来像SUM()或AVG()这样的聚合函数是我需要的，但是我不知道T-SQL是否有一个。我使用的是SQL Server 2005。

2 个解决方案

#1

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

不，对于SQL Server 2005和以上版本，您需要做如下操作:

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

此外，要注意，并不是所有的XML路径连接都会正确地处理XML特殊字符，就像我上面的示例所做的那样。

#2

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

Sql Server中没有内置函数，但是可以通过编写用户定义的聚合来实现。本文在SQL Server示例中提到了这样一个函数: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

作为一个示例，我包含了连接聚合的代码。要使用它，请在Visual Studio中创建一个数据库项目，添加新的SqlAggregate并使用下面的示例替换代码。部署之后，应该在数据库中找到一个新的程序集和一个聚合函数连接

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

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

#1