2017 年之前 SQL Server 的 String_agg

问题描述：

谁能帮我使这个查询适用于 SQL Server 2014?

Can anyone help me make this query work for SQL Server 2014?

这适用于 Postgresql，可能适用于 SQL Server 2017.在 Oracle 上，它是 listagg 而不是 string_agg.

This is working on Postgresql and probably on SQL Server 2017. On Oracle it is listagg instead of string_agg.

这是SQL:

select 
    string_agg(t.id,',') AS id
from 
    Table t

我在网站上检查了一些应该使用的 xml 选项，但我无法理解.

I checked on the site some xml option should be used but I could not understand it.

答

在 SQL Server 2017 之前，您可以:

In SQL Server pre-2017, you can do:

select stuff( (select ',' + cast(t.id as varchar(max))
               from tabel t
               for xml path ('')
              ), 1, 1, ''
            );

stuff() 的唯一目的是去除开头的逗号.工作由for xml path完成.

The only purpose of stuff() is to remove the initial comma. The work is being done by for xml path.

2017 年之前 SQL Server 的 String_agg

相关推荐