--先建立一个合并函数
create function getstr(@bh char(10))
returns varchar(4000)
as
begin
declare @str varchar(4000)
set @str=''
select @str=@str+gs+',' from 表a where jsdm=@bh set @str=left(@str,len(@str)-1)
return @str
end
--然后调用
select jsdm,dbo.getstr(jsdm) from 表a group by jsdm
create function getstr(@bh char(10))
returns varchar(4000)
as
begin
declare @str varchar(4000)
set @str=''
select @str=@str+gs+',' from 表a where jsdm=@bh set @str=left(@str,len(@str)-1)
return @str
end
--然后调用
select jsdm,dbo.getstr(jsdm) from 表a group by jsdm
create function getstr(@bh char(10))
returns varchar(4000)
as
begin
declare @str varchar(4000)
set @str=''
select @str=@str+gs+',' from 表a where jsdm=@bh set @str=left(@str,len(@str)-1)
return @str
end
--然后调用
select jsdm,dbo.getstr(jsdm) as strings into 表b from 表a group by jsdm
create function fmerg(@id varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+gs from 表A where jsdm=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go--调用自定义函数得到结果
select distinct jsdm,dbo.fmerg(jsdm) from 表A
create function test(@id varchar(200))
returns varchar(8000)
as
begin
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+gs+',' from 表a where jsdm=@id
set @sql=left(@sql,len(@sql)-1)
return(@sql)
end--执行
select distinct jsdm,dbo.test(jsdm) as strings from 表a
returns varchar(8000) --如果数据大,可以改成大一点的长度
as
begin
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+gs from a where jsdm=@id
return(@sql)
end
go执行:
selecdt jsdm,dbo.f_name(jsdm) as [strings] from a group by jsdm
returns varchar(8000) --如果数据大,可以改成大一点的长度
as
begin
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+gs from a where jsdm=@jsdm
return(@sql)
end
go执行:
select jsdm,dbo.f_name(jsdm) as [strings] into b from a group by jsdmselect * from b
--这个问题太多人问了.
--方法1.用自定义函数,适用于sql2000
create function f_merg(@jsdm varcahr(10))
returns varchar(8000)
as
begin
declare @re varchar(8000)
set @re=''
select @re=@re+','+gs from 表a where jsdm=@jsdm
set @re=substring(@re,2,8000)
return(@re)
end
go--调用自定义实现你的要求:
select jsdm,dbo.f_merg(jsdm) strings into 表b from 表a group by jsdm
select jsdm,cast(gs as varchar(8000)) as strings into 表b from 表a order by jsdm--更新strings
declare @jsdm varchar(10),st varchar(8000)
update 表b set st=case jsdm when @jsdm then st+','+stings else strings end
,stings=@jsdm,@jsdm=jsdm--删除多余的数据
delete from 表b where strings<>(select top 1 strings from 表b a where a.jsdm=jsdm order by len(strings) desc)