建立一个逗号分隔的列表?

2021-12-09 00:00:00 csv sql tsql sql-server

我正在尝试使用 SQL 来构建以逗号分隔的 cat_id 列表

I'm tryin to use SQL to build a comma separated list of cat_id's

代码是:

declare     @output varchar(max)
set         @output = null;
select @output = COALESCE(@output + ', ', '') + convert(varchar(max),cat_id)

将 '' 更改为 null，仍然相同.但我得到的输出是这样的:

edit: changed '' to null, STILL same. but the output im getting is like so:

, 66 , 23

前导逗号不应该在那里.我错过了什么?

the leading comma should not be there. What have i missed?

推荐答案

您使用 SQL 2005 吗?为 Rob Farley 提供道具，他最近向我展示了这个:

Are you on SQL 2005? With props to Rob Farley who showed me this just recently:

SELECT stuff((
    SELECT ', ' + cast(cat_id as varchar(max))
    FROM categories
    FOR XML PATH('')
    ), 1, 2, '');

内部查询(使用 FOR XML PATH(''))选择以逗号分隔的类别 ID 列表，以,"开头.外部查询使用 stuff 函数来删除前导逗号和空格.

The inside query (with FOR XML PATH('')) selects a comma-separated list of category IDs, with a leading ", ". The outside query uses the stuff function to remove the leading comma and space.

我没有一个方便的 SQL 实例来测试这个，所以它来自内存.您可能需要使用一些东西参数等才能让它按照您想要的方式工作.

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.

相关文章