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

Discuss this article: 9 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

Related Articles

Using Dynamic SQL in Stored Procedures (7 March 2011)

Joining to the Next Sequential Row (2 April 2008)

Writing Outer Joins in T-SQL (11 February 2008)

Aggregating Correlated Sub-Queries (23 October 2007)

How to Use GROUP BY with Distinct Aggregates and Derived tables (31 July 2007)

How to Use GROUP BY in SQL Server (30 July 2007)

Returning Complex Data from User-Defined Functions with CROSS APPLY (11 June 2007)

Returning a week number for any given date and starting fiscal month (2 May 2007)

Other Recent Forum Posts

How to get data for First Working Day Of The Week (1 Reply)

incremented updated (2 Replies)

get data from unknow at desing tables (7 Replies)

Unable to Create a Publication (8 Replies)

Add mutiplt field with auto generate number? (6 Replies)

Best way to Bulk Insert (1 Reply)

TRIGGER WITH BCP (7 Replies)

Football (soccer) scoring system (5 Replies)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -