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?
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) 
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.
