Converting Multiple Rows into a CSV String
By Chris Miller
on 15 August 2000
| 9 Comments
| Tags: Queries
Joern asks How do I aggregate data from multiple rows into a delimited list?
Joern writes
"Dear SQL-Team: I want to condense several rows of a select.
mytable :
1 a
2 b
2 c
2 d
3 a
so that my select should result in
1 a
2 b,c,d
3 a
Of course it can be done with ASP (actually I do it this way), or in the db with fetching. But is there a simpler way to do it?"I'm assuming by "fetching" you mean using cursors. We've run into this problem on several occasions, and the best we've come up with is to build a standard stored proc that takes as parameters a SQL Statement and opens a cursor and builds the string. Definitely not a very clean way to do it, but really the only way to do it in SQL Server at this point. Usually we use this for building a comma-delimited list for use in an "IN" clause for a query, specifically for search applications that allow multiple-select criteria. Here's what we use. The "@IsChar" is used to determine whether or not we need to put quotation marks around the returned strings (remember, we use this to build IN clauses). You may want to read up on using OUTPUT parameters in stored procs before using this stored proc. This won't do exactly what you specify, but it's a good starting point.
rocketscientist.
CREATE procedure sp_MakeCharList( @codelistselect varchar(1000), @delimitedlist varchar(8000) output, @IsChar bit = 0)
AS
begin
set nocount on
set rowcount 0
declare @vcCurrentCol varchar(255)
declare @vcCurrentList varchar(8000)
declare @RC int
create table #temptable (code varchar(255))
insert into #temptable (code)
exec(@codelistselect)
set @RC = @@RowCount
if @RC = 0
begin
set @DelimitedList = null
return 0
end
declare SysCols insensitive scroll cursor
for select code from #temptable
for read only
open SysCols
fetch next from SysCols into @vcCurrentCol
select @vcCurrentList = ''
while @@Fetch_Status = 0
begin
if @IsChar = 1
select @vcCurrentList = @vcCurrentList + quotename(@vcCurrentCol, '''') + ', '
else
select @vcCurrentList = @vcCurrentList + @vcCurrentCol + ', '
fetch next from SysCols into @vcCurrentCol
end
close SysCols
deallocate SysCols
if @vcCurrentList = ''
select @DelimitedList = null
else
--Remove the last ', '
select @delimitedList = Substring(@vcCurrentList, 1, datalength(@vcCurrentList) - 2)
end(Please also see this
article on a set based approach to this problem.)
