最简单的做法是: 1、全匹配: Select * from Temp1 Where FacName like "%北京%";或: 2、Select * from Temp1 Where FacName like "%北京"; 或: 3、Select * from Temp1 Where FacName like "北京%";
利用cursor可以處理任意多行的資料. select * into #temp2 from #temp1 where 1=2 declare #temp1_cursor scroll cursor for select * from #temp1 order by mcode open #temp1_cursor declare @mcode varchar(9) declare @supp_id varchar(4) declare @msg varchar(10) declare @i int declare @j int select @i=(select count(*) from #temp1) set @j=0 while @j<@i begin fetch next from #temp1_cursor into @mcode,@supp_id,@msg if exists(select * from #temp2 where mcode=@mcode and supp_id=@supp_id) update #temp2 set facname=facname+','+@msg where mcode=@mcode and supp_id=@supp_id else insert #temp2 values(@mcode,@supp_id,@msg) set @j=@j+1 end CLOSE #temp1_cursor DEALLOCATE #temp1_cursor select * from #temp2 剛剛測試OK.
select mcode ,max(supp_id),dbo.库名.自定义函数fuction(mcode) from from temp1 group by mcode 在自定义函数fuction(mcode)返回是如“北京,深圳” 的字符串
1、全匹配:
Select * from Temp1 Where FacName like "%北京%";或:
2、Select * from Temp1 Where FacName like "%北京";
或:
3、Select * from Temp1 Where FacName like "北京%";
select * into #temp2 from #temp1 where 1=2
declare #temp1_cursor scroll cursor for
select * from #temp1 order by mcode
open #temp1_cursor
declare @mcode varchar(9)
declare @supp_id varchar(4)
declare @msg varchar(10)
declare @i int
declare @j int
select @i=(select count(*) from #temp1)
set @j=0
while @j<@i
begin
fetch next from #temp1_cursor into @mcode,@supp_id,@msg
if exists(select * from #temp2 where mcode=@mcode and supp_id=@supp_id)
update #temp2 set facname=facname+','+@msg where mcode=@mcode and supp_id=@supp_id
else
insert #temp2 values(@mcode,@supp_id,@msg)
set @j=@j+1
end
CLOSE #temp1_cursor
DEALLOCATE #temp1_cursor
select * from #temp2
剛剛測試OK.