Wednesday, February 17, 2010

Concatenating rows as a alternative to using a cursor

As I was reading the "question of the day" on SqlServerCentral.com I came across a simple but very effective alternative solution to using a cursor by concatenating rows.

declare @table table (id int, [name] varchar(10), gender char(1))
declare @MaleNames varchar(255)

insert into @table values (1,'John','M')
insert into @table values (2,'Sue','F')
insert into @table values (3,'Jane','F')
insert into @table values (4,'Mark','M')
insert into @table values (5,'Bill','M')

select @MaleNames = IsNull(@MaleNames + ', ','') + [name] from @table where gender = 'M'

Select @MaleNames as MalePatients

0 comments:

Post a Comment