问题描述
<块引用>可能的重复:
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 是否具有连接字符串的聚合函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！