I would see two ways of doing this - both involve a temporary table.

Let's create the table as

create table #comCity (

seqNo int,

cityList varchar(2000),

lastseq int

)

Option 1

insert into #comCity

select t1.seqNo, t2.location, t2.seqorder

from table1 t1 (nolock)

inner join table2 t2 (nolock)

on t2.seqNo = t1.seqNo and t2.seqorder =

( select min(seqorder) from table2 t2a (nolock) where t2a.seqNo = t1.seqNo )

while @@ROWCOUNT > 0

begin

update #comCity set

cityList = cityList + ',' + t2.location,

lastseq = t2.seqorder

from table2 t2 (nolock)

where t2.seqNo = #comCity.seqNo and t2.seqorder =

( select min(seqorder) from table2 t2a (nolock) where t2a.seqNo = #comCity.seqNo

and t2a.seqorder > #comCity.lastseq )

end

Option 2

declare @seqNo int, @cityList varchar(2000)

DECLARE seqCur CURSOR

FOR SELECT seqNo from table1

open seqCur

FETCH NEXT FROM seqCur INTO @seqNo

WHILE @@FETCH_STATUS = 0

BEGIN

SET @cityList = ''

SELECT @cityList = @cityList + CASE WHEN @cityList = '' THEN '' ELSE ',' END + Location

FROM table2 (NOLOCK)

WHERE seqNo = @seqNo

INSERT INTO #comCity VALUES ( @seqNo, @cityList, 0 )

FETCH NEXT FROM seqCur INTO @seqNo

END

deallocate seqCur

Sorry - I changed the sequence into an integer for my test, but I hope you get the point.

Guarddata-