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-