Dave Burke - SQL : Creating single comma-dimmed field from multiple records

SQL : Creating single comma-dimmed field from multiple records

This is an example of generating a comma-delimited string of values in SQL. The items are originally stored as separate rows in a table of the format

ID PID Project Type

1 200 Really Fun
2 200 Kinda Fun
3 200 Wildy Fun
4 201 Not Fun

The result is

PID | Project Description | Project Types

200 | Some Fun Project | Really Fun, Kinda Fun, Wildy Fun

What we want, in other words, are all project types located in another table, shown as a single varchar() field. "Really Fun, Kinda Fun, Wildy Fun"

declare pidcursor CURSOR
GLOBAL
for select pid, project_type from v_projtypes order by pid
open pidcursor

declare @project_type varchar(255)

select @totalcount = count(*) from v_projtypes
select @counter = 1

fetch next from pidcursor into @pidchk, @project_type

while (@counter <= @totalcount) and (@@fetch_status=0)
begin
update #tmp_projtypes set project_types = project_types + ', ' + @project_type where pid = @pidchk
fetch next from pidcursor into @pidchk, @project_type
end

close pidcursor
deallocate pidcursor

Comments (2) | Post RSS RSS comment feed

Posted on 5/8/2005 10:17:00 AM by Dave Burke
Categories: .NET
Tags:

5/9/2005 7:09:57 AM Permalink

Wouldn't it be more efficient to accumulate the @project_type values in a varchar variable while looping through the cursor result set?  Then you could use a single insert or update statement to update the destination table.  Something like:

while (@@fetch_status=0)
begin
  if @counter = 1 begin
    SET @all = @project_type
    SELECT @counter = 2
  END
  ELSE SELECT @all = @all + ', ' + @project_type
  fetch next from pidcursor into @pidchk, @project_type
end

Chris Miller |

5/9/2005 7:46:47 AM Permalink

Hmm. I spent some time working with your suggestion, since it definitely makes sense. Ran as is with "update #tmp_projtypes set project_types = @all where pid = @pidchk" before the fetch next caused each rows results to be appended to the previous row. I could never figure out how to reset @all prevent that. Thanks very much for your input, Chris.

daveburke |

Dave Burke : Freelance .NET Web Developer specializing in Online Communities

SQL : Creating single comma-dimmed field from multiple records

Related posts

Comments (2) -