SQL Server 2008行到1个CSV字段 [英] SQL Server 2008 Rows to 1 CSV field

问题描述

我们在SQL Server 2008上，我试图找出是否有一个方法让一个存储过程在1个CSV字段中返回我的结果

We're on SQL Server 2008 and I'm trying to figure out if there's a way to have a stored procedure return my results in 1 CSV field

示例：

SELECT TOP 4 carModels
FROM dbo.Models

会返回

Jeep
Honda
Mitsubishi
Ford

我想在1个字段中返回这样：
Jeep，Honda，Mitsubishi，Ford

I would like this returned in 1 field like so: Jeep,Honda,MitsubisFord

我知道我们可以使用程序集，临时表或服务器端代码来完成这项工作， 。

I know we can do this with an assembly, temp tables, or server side code but would prefer not to go that route. Are there any tips / tricks you could suggest to get the result I'm looking for?

推荐答案

请尝试以下操作：

DECLARE @x varchar(8000)

SELECT TOP 4
    @x=ISNULL(@x+', ','')+carModels
    FROM dbo.Models

SELECT @x AS carModels

EDIT 同样的答案，但这里是完整的代码来测试...

EDIT same answer as above, but here is complete code to test it out...

declare @Models table (RowID int not null primary key identity(1,1), carModels varchar(20))
insert into @Models values ('Jeep')
insert into @Models values ('Honda')
insert into @Models values ('Mitsubishi')
insert into @Models values ('Ford')
insert into @Models values ('Mazda')

DECLARE @x varchar(8000)
SET @x=null
SELECT TOP 4
    @x=ISNULL(@x+', ','')+carModels
    FROM @Models

SELECT @x AS carModels

输出：

carModels
----------------------------------
Jeep, Honda, Mitsubishi, Ford

(1 row(s) affected)

这篇关于SQL Server 2008行到1个CSV字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！