T-SQL是否具有连接字符串的聚合函数？ [英] Does T-SQL have an aggregate function to concatenate strings?

问题描述

可能存在重复：

SQL Server 2000中的Implode类型函数？

连接行值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需要编写一个将BuildingNames组合在一起的查询，并显示一个PollNumbers列表，如下所示：
$ b

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

我如何在T-SQL中做到这一点？我宁愿不要为此写一个存储过程，因为它看起来有点过分，但我不完全是一个数据库人员。看起来像SUM（）或AVG（）这样的聚合函数是我需要的，但我不知道T-SQL是否有。我正在使用SQL Server 2005.

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.

推荐答案

不，对于SQL Server 2005来说，你需要这样做：

no, for SQL Server 2005 and up, 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)

另外，小心，不是所有的 FOR XML PATH 连接都能正确处理XML特殊字符，就像我上面的例子那样。

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

这篇关于T-SQL是否具有连接字符串的聚合函数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！