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

问题描述

<块引用>

可能的重复:
SQL Server 2000 中的内爆类型函数?
连接行值 T-SQL

我正在查询的视图如下所示:

<块引用>

BuildingName PollNumber------------ ----------富中心 12富中心 13富中心 14酒吧大厅 15酒吧大厅 16巴兹学校 17

我需要编写一个查询，将 BuildingNames 组合在一起并显示 PollNumbers 列表，如下所示:

<块引用>

BuildingName PollNumbers------------ -----------富中心 12, 13, 14酒吧大厅 15, 16巴兹学校 17

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

解决方案

适用于 SQL Server 2017 及更高版本:

STRING_AGG()

设置无计数；声明@YourTable 表(RowID int、HeaderValue int、ChildValue varchar(5))插入@YourTable VALUES (1,1,'CCC')插入@YourTable VALUES (2,2,'B<&>B')插入@YourTable VALUES (3,2,'AAA')插入@YourTable VALUES (4,3,'<br>')插入@YourTable VALUES (5,3,'A & Z')取消计数选择t1.HeaderValue，东西((选择', ' + t2.ChildValue来自@YourTable t2WHERE t1.HeaderValue=t2.HeaderValue按 t2.ChildValue 排序FOR XML PATH(''), 类型).value('.','varchar(max)'),1,2, '') 作为子值来自@YourTable t1按 t1.HeaderValue 分组选择HeaderValue, STRING_AGG(ChildValue,', ')来自@YourTable按标题值分组

输出:

HeaderValue------------ -------------1个CCC2 B<&>B，AAA3 <br>, A &Z(受影响的 3 行)

对于 SQL Server 2005 和 2016 之前的版本，您需要执行以下操作:

--与 FOR XML 连接并消除控制/编码字符扩展 "& < >"设置不计；声明@YourTable 表(RowID int、HeaderValue int、ChildValue varchar(5))插入@YourTable VALUES (1,1,'CCC')插入@YourTable VALUES (2,2,'B<&>B')插入@YourTable VALUES (3,2,'AAA')插入@YourTable VALUES (4,3,'<br>')插入@YourTable VALUES (5,3,'A & Z')取消计数选择t1.HeaderValue，东西((选择', ' + t2.ChildValue来自@YourTable t2WHERE t1.HeaderValue=t2.HeaderValue按 t2.ChildValue 排序FOR XML PATH(''), 类型).value('.','varchar(max)'),1,2, '') 作为子值来自@YourTable t1按 t1.HeaderValue 分组

输出:

HeaderValue ChildValues----------- -------1个CCC2 AAA、B<&>B3 <br>, A &Z(3 行受影响)

另外，请注意，并非所有 FOR XML PATH 连接都能像我上面的示例那样正确处理 XML 特殊字符.

Possible Duplicates:
Implode type function in SQL Server 2000?
Concatenate row values 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.

解决方案

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.

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