Build Comment and associate to an owner without using Cursor 1

[MeanGreen]

I have a table with multiple comments that I want to merge into one comment per owner WITHOUT using a CURSOR. Here is my sample table:

create table #test
(Owner char(1),
Seq int,
comment varchar(25))

insert #test values ('A',1,'This is')
insert #test values ('A',2,'a')
insert #test values ('A',3,'Test')
insert #test values ('B',1,'Second of')
insert #test values ('B',2,'many test')
insert #test values ('C',1,'Third test only')

What I want to see for output is:

A, This is a Test
B, Second of many test
C, Third test only

Again, this is without using a CURSOR. I can get the comment ONLY per owner with the following code:

declare @comment varchar(2500)
set @comment = ''
select @comment = @comment + comment + ' '
from #test
where owner = 'A'
order by owner,seq
select @comment

Any help here would be appreciated.

 

[JamesLean]

Build a function that returns the full comment for a particular user, using the code you have already written. Then use that function in your select:

SELECT DISTINCT owner, dbo.getComment(owner) AS comment
FROM table

--James