SQL 建立一个逗号分隔的列表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1564980/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Building a comma separated list?
提问by pablo
I'm tryin to use SQL to build a comma separated list of cat_id's
我正在尝试使用 SQL 来构建以逗号分隔的 cat_id 列表
the code is:
代码是：
declare @output varchar(max)
set @output = null;
select @output = COALESCE(@output + ', ', '') + convert(varchar(max),cat_id)
edit: changed '' to null, STILL same. but the output im getting is like so:
编辑：将 '' 更改为 null，仍然相同。但我得到的输出是这样的：
, 66 , 23
the leading comma should not be there. What have i missed?
前导逗号不应该在那里。我错过了什么？
回答by Matt Hamilton
Are you on SQL 2005? With props to Rob Farleywho showed me this just recently:
您使用的是 SQL 2005 吗？给Rob Farley 的道具，他最近向我展示了这个：
SELECT stuff((
SELECT ', ' + cast(cat_id as varchar(max))
FROM categories
FOR XML PATH('')
), 1, 2, '');
The inside query (with FOR XML PATH('')) selects a comma-separated list of category IDs, with a leading ", ". The outside query uses the stufffunction to remove the leading comma and space.
内部查询 (with FOR XML PATH('')) 选择以逗号分隔的类别 ID 列表，并带有前导“,”。外部查询使用stuff函数删除前导逗号和空格。
I don't have an SQL instance handy to test this, so it's from memory. You may have to play with the stuff parameters etc to get it to work exactly how you want.
我没有一个方便的 SQL 实例来测试这个，所以它来自内存。您可能必须使用东西参数等才能让它完全按照您的意愿工作。
回答by The Chairman
COALESCE Returns the first nonnull expression among its arguments
COALESCE返回其参数中的第一个非空表达式
First argument @output + ', 'is never null (unless you initialize @outputas null AND set CONCAT_NULL_YIELDS_NULLto ON), so it's always returned.
第一个参数@output + ', '永远不会为空（除非您初始化@output为空并设置CONCAT_NULL_YIELDS_NULL为ON），因此它始终返回。
回答by pablo
And sometimes...
而有时...
you have to answer your own question
你必须回答你自己的问题
declare @output varchar(max)
select @output = case when (@output is null) then '' else ', ' END + convert(varchar(max),cat_id)
回答by Michael Buen
declare @output varchar(max)
select @output = coalesce
(
@output + ', ' + convert(varchar(max),cat_id),
convert(varchar(max),cat_id)
)
from yourTableHere
print @output
回答by manji
check @outputvalue just before the execution of this query, I think it's not equal to NULLbut to '' (empty string)
@output在执行此查询之前检查值，我认为它不等于NULL而是 ''（空字符串）
EDIT: (after the @auth edited the question)
编辑：（在@auth 编辑了问题之后）
now I'm sure it's '',
现在我确定它是 '',
you have to initialize it to NULL
你必须将它初始化为 NULL
to do it independently of CONCAT_NULL_YIELDS_NULL, use the old CASE WHEN:
要独立于CONCAT_NULL_YIELDS_NULL，请使用旧的CASE WHEN：
select @output = NULL
select @output = CASE WHEN @output IS NULL THEN '' ELSE @output+', ' END + value
回答by Andomar
Did you initialize @output to an empty string? COALESCE will only work if it's a NULL string.
您是否将@output 初始化为空字符串？COALESCE 仅在它是 NULL 字符串时才有效。
回答by Guffa
What you are doing wrong is that @output is not null from start, but an empty string. Set @output to null before the loop (or if it's not used since it's declared just don't assign an empty string to it).
你做错的是@output 从一开始就不是空的，而是一个空字符串。在循环之前将 @output 设置为 null（或者如果它没有被使用，因为它被声明了只是不给它分配一个空字符串）。
回答by Tom Winter
Not sure if this applies exactly to what you're looking for, but I found this right at the same time I found your questions. I use the second solution with FOR XML PATH, which Matt Hamilton mentioned above. It's worked great for me.
不确定这是否完全适用于您正在寻找的内容，但我在发现您的问题的同时发现了这一点。我将第二种解决方案与 FOR XML PATH 一起使用，马特·汉密尔顿在上面提到过。它对我很有用。
Concatenating Rows - By Carl P. Anderson, 2009/10/14
连接行 - Carl P. Anderson，2009/10/14
回答by Nigel Pearson
/osp/install/idp/bin/sqlminus "select ri || ',' ||name|| ',' || numports || ',' || ascii(OVRONSET) from sdfctigw.ivrgrp where GRP_BEP is not null;" | sort -h
1,COMO INTERNAL 2,700,90
7,LOADIVR,10,80
10,SPEECH_IVR_PROD,600,95
