June 2, 2003 at 3:51 am
Can anyone help with a join as per example below to concatenate a field in the example surrogate table
Trip table
Seq Num
Product
Van
Driver
Consignment Calls
Foreign Seq
Location
Data:
Trip table
123ProductAWER1234Joe
124ProductB3535TREMick
Consignment Calls
123 London
123Brighton
123Portsmouth
124Leicester
124Manchester
124Leicester
The result I am after is:
123, 'product,'WER1234', 'London,Brighton, Portsmouth'
124, 'productB', '3535TRE', 'Mick', 'Leicester, Manchester, Leicester'
June 2, 2003 at 6:29 am
This should work,
use pubs
Create Table Trip(
SeqNum int
,Product varchar (50)
,Van varchar(50)
,Driver varchar(50)
)
Create table ConsignmentCalls(
ForeignSeq int
,Location varchar( 50 )
)
insert into Trip
Select 123, 'ProductA', 'WER1234', 'Joe'
insert into Trip
Select 124, 'ProductB', '3535TRE', 'Mick'
Insert into ConsignmentCalls select 123, 'London'
Insert into ConsignmentCalls select 123, 'Brighton'
Insert into ConsignmentCalls select 123, 'Portsmouth'
Insert into ConsignmentCalls select 124, 'Leicester'
Insert into ConsignmentCalls select 124, 'Manchester'
Insert into ConsignmentCalls select 124, 'Leicester'
/*
The result I am after is:
123, 'product,'WER1234', 'London,Brighton, Portsmouth'
124, 'productB', '3535TRE', 'Mick', 'Leicester, Manchester, Leicester'
*/
declare @Trip int
,@Calls varchar(250)
set @Calls = ''
Select @TRip = min( seqnum ) from Trip
Create Table #results(
SeqNum int
,Product varchar (50)
,Van varchar(50)
,Driver varchar(50)
,Calls varchar(2000)
)
While @Trip <= (select Max( seqnum ) from Trip ) Begin
Set @Calls = ''
Select @Calls = @calls + Location + ', '
From ConsignmentCalls
Where ForeignSeq = @TRip
set @Calls = Left( @calls, len(@calls) - 1 )
Insert into #Results
Select *, @calls From Trip where seqnum = @Trip
set @trip = @trip +1
End
Select Convert( varchar(9), seqnum ) + ', ' +
Product + ', ' +
Van +', ' +
Driver + ', ' +
Calls
From #results
Drop Table ConsignmentCalls
Drop Table Trip
Drop Table #Results
June 2, 2003 at 3:31 pm
not sure this suits you needs but you might try something like this:
use pubs
Create Table Trip(
SeqNum int
,Product varchar (50)
,Van varchar(50)
,Driver varchar(50)
)
Create table ConsignmentCalls(
ForeignSeq int
,Location varchar( 50 )
)
insert into Trip
Select 123, 'ProductA', 'WER1234', 'Joe'
insert into Trip
Select 124, 'ProductB', '3535TRE', 'Mick'
Insert into ConsignmentCalls select 123, 'London'
Insert into ConsignmentCalls select 123, 'Brighton'
Insert into ConsignmentCalls select 123, 'Portsmouth'
Insert into ConsignmentCalls select 124, 'Leicester'
Insert into ConsignmentCalls select 124, 'Manchester'
Insert into ConsignmentCalls select 124, 'Leicester'
-- declare variables
declare @p char(1000)
declare @top int
declare @m int
declare @sm-2 int
declare @v-2 varchar(50)
declare @d char(5)
declare @name char(50)
-- Print Report Heading
print 'SeqNum ' + 'Qualifications'
print '------- -------- ------- ----- -------------------------------------------------'
set @p = ''
select top 1 @top =SeqNum from trip order by SeqNum desc
-- set @m to the first id number
select top 1 @m = SeqNum, @name=product, @v-2=van, @d=driver from trip order by SeqNum
-- Process until no more items
while @m <= @top
begin
-- string together all items with a comma between
select @p = rtrim(@p) + ', '+ Location
from ConsignmentCalls a
where ForeignSeq = @m
-- print detail row -- could insert into a temp table is you really want to select from a table here
print cast(@m as char(7)) + ' ' + rtrim(@name) + ' ' + @v-2 + ' '
+ @d + ' ' + rtrim(substring(@p,3,len(@p)))
-- increment clientid number
select top 1 @m = SeqNum, @name=product, @v-2=van, @d=driver from trip where SeqNum > @m order by SeqNum
set @p = ''
if @m = @sm-2 set @m = @top + 1
end
drop table trip,ConsignmentCalls
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply