问题描述
我有一个查看我查询的内容如下所示：
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是否具有连接字符串的聚合函数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！