July 7, 2003 at 5:32 am
I'm trying to get:
'1234', 'Rotterdam,Gothenburg,Tallinn'
'1235', 'Liverpool,Malaga'
from
Table 1
Seq
'1234'
'1235'
'1236'
Table 2
Seq Location Order
1234 Gothenburg 2
1234 Rotterdam 1
1234 Tallinn 3
1235 Liverpool 1
1235 Malaga 2
etc..
Any ideas ?
July 7, 2003 at 11:09 am
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-
July 8, 2003 at 3:50 pm
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply