问题是:
表一: 表二:
MAT 001 XXX MAT 001 DID 1 ABC
MAT 002 YYY MAT 001 DID 2 DEF
MAT 001 WS 1 abc
MAT 001 WS 2 def
MAT 002 DID 1 ABC
MAT 002 WS 1 abc结果:
MAT 001 XXX ABCDEF abcdef
MAT 002 YYY ABC abc相同的标志(如“DID”)按顺序合成为 一个字段内容
表一: 表二:
MAT 001 XXX MAT 001 DID 1 ABC
MAT 002 YYY MAT 001 DID 2 DEF
MAT 001 WS 1 abc
MAT 001 WS 2 def
MAT 002 DID 1 ABC
MAT 002 WS 1 abc结果:
MAT 001 XXX ABCDEF abcdef
MAT 002 YYY ABC abc相同的标志(如“DID”)按顺序合成为 一个字段内容
MAT 001 XXX MAT 001 DID 1 ABC
MAT 002 YYY MAT 001 DID 2 DEF
MAT 001 WS 1 abc
MAT 001 WS 2 def
MAT 002 DID 1 ABC
MAT 002 WS 1 abc
结果:
MAT 001 XXX ABCDEF abcdef
MAT 002 YYY ABC abc
f1 f2 f3 f1 f2 f3 f4 f5
MAT 001 XXX MAT 001 DID 1 ABC
MAT 002 YYY MAT 001 DID 2 DEF
MAT 001 WS 1 abc
MAT 001 WS 2 def
MAT 002 DID 1 ABC
MAT 002 WS 1 abc
declare @max as int
declare @min as int
select @max=max(f4),@min=min(f4) from table2
declare @i as int
select @i=@minselect distinct table1.*,table2.f3 as f4 ,space(1000) as f5
into #temp
from table1 join table2 on table1.f1=table2.f1 and table1.f2=table1.f2while @i<=@max
begin
update t1 set t1.f5=rtrim(t1.f5)+t2.f5
from #temp t1 join table2 t2 on t1.f1=t2.f1 and t1.f2=t2.f2 and t1.f4=t2.f3
where t2.f4=@i select @i=@i+1
endCREATE TABLE dbo.#Temp1 ([f1] [varchar] (50) NULL , [f2] [varchar] (50) NULL ,
[f3] [varchar] (50) NULL ,
) ON [PRIMARY] insert into #temp1 ( f1,f2,f3)
select distinct f1,f2,f3 from #tempdeclare @str as varchar(100)
declare @sqlstr as varchar(5000)
select @sqlstr=''
select @str=''declare aaa cursor for select distinct f4 from #temp
open aaafetch next from aaa into @strwhile @@fetch_status=0
begin
select @sqlstr='alter table #temp1 add '+@str+' varchar (50) null'
exec (@sqlstr)
select @sqlstr='update #temp1 set '+@str+'=#temp.f5 from #temp1 join #temp on '+
'#temp1.f1=#temp.f1 and #temp1.f2=#temp.f2 and #temp1.f3=#temp.f3 and #temp.f4='''+@str+''''
print @sqlstr
exec (@sqlstr)
fetch next from aaa into @str
end
select * from #temp1
drop table #temp1
close aaa
deallocate aaa