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)
*/--------------------------------------
Signature is NULL