sql - 将 SELECT 的行折叠成字符串的最佳方法是什么？

Question

在 SQL 语句(或过程)中，我想将此表的行折叠成单个逗号分隔的字符串。

simpleTable

id  value
--  ----- 
1    "a"
2    "b"
3    "c"

折叠到:

"a, b, c"

Answer 1

您可以在查询中使用嵌入的“set”语句进行连接:

declare @combined varchar(2000)select @combined = isnull(@combined + ', ','') + isnull(value,'')from simpleTableprint @combined

(Note that the first isnull() initialises the string, and the second isnull() is especially important if there's any chance of nulls in the 'value' column, because otherwise a single null could wipe out the whole concatenation)

(edited code and explanation after comments)

Edit (ten years later):

SQL Server 2017 introduced the STRING_AGG() function which provides an official way of concatenating strings from different rows. Like other aggregation functions such as COUNT(), it can be used with GROUP BY.

So for the example above you could do:

select string_agg(value, ', ')
from simpleTable

如果您有其他列并且想要连接该列的值，则可以添加“group by”子句，例如:

select someCategory, string_agg(value, ', ') as concatValues
from simpleTable
group by someCategory

注意 string_agg 仅适用于 SQL 2017 及更高版本。