SQL Server 2008 Rows to 1 CSV field

Question

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

for example:

SELECT TOP 4 carModels
FROM dbo.Models

would return

Jeep
Honda
Mitsubishi
Ford

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

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?

Answer 1

The following might work. I don't have SQLServer today to verify.

DECLARE @Str VARCHAR(8000)
    SET @Str = SPACE(0)
    SELECT @Str = @Str + ',' + SUBSTRING(@Str + Models.Name, 1, 10)
      FROM dbo.Models
    PRINT @Str

Is this something you can do on the client? If I had the choice I would probably remove it from the data layer and put it on the client, formatting it to CSV when I needed it.

Answer 2

try this:

DECLARE @x varchar(8000)

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

SELECT @x AS carModels

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

output:

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

(1 row(s) affected)