This is a really interesting question!

Here's how to do it without the cursor, but it still uses 1 temp table (will need 2 if Table 2 doesn't have an identity column). Script's to create test data at the bottom. These loops are super cheap...checking 1=1 is 50% of the costs!

----------------------------------

Declare @iID int,

@iSeq int

declare @Table1 Table (Seq int Primary Key, Location varchar(500))

Select @iSeq = 0,

@iID = 0

Insert @Table1 (Seq)

Select Seq

From #Table1

select * from @Table1

While 1=1

begin

Select top 1 @iSeq = Seq

From @Table1

Where Seq > @iSeq

order by Seq

IF @@Rowcount = 0 Break

While 1=1

begin

Select top 1 @iID = [ID]

From #Table2

Where Seq = @iSeq and [ID] > @iID

order by [ID]

IF @@Rowcount = 0 Break

Update @Table1

Set Location = isnull((t1.Location + ','), '') + t2.Location

From #Table2 t2

JOIN @Table1 t1 on t2.Seq = t1.Seq

Where t2.[ID] = @iID

end

end

Select * from @Table1 Where Location is not null

/*------------------------------

--To Create Test Data

If object_id('tempdb..#Table1') is null Create Table #Table1 (Seq int)

Insert #Table1 Values (1234)

Insert #Table1 Values (1235)

Insert #Table1 Values (1236)

If object_id('tempdb..#Table2') is null Create Table #Table2 ([ID] int identity, Seq int, Location varchar(25), [order] int)

Insert #Table2 Values (1234, 'Gothenburg', 2)

Insert #Table2 Values (1234, 'Rotterdam', 1)

Insert #Table2 Values (1234, 'Tallinn', 3)

Insert #Table2 Values (1235, 'Liverpool', 1)

Insert #Table2 Values (1235, 'Malaga', 2)

*/--------------------------------------