update

[lihongf]

表a
jsdm gs
01-0001 0000001
01-0001 0000005
01-0001 0000006
01-0002 0000011
01-0003 0000054
01-0003 0000048
01-0003 0000068
01-0003 0000018
01-0003 0000059
....
表b
jsdm strings
01-0001 0000001,0000005,0000006
01-0002 0000011
01-0003 0000054,0000048,0000068,0000018,0000059
..

如何将表a的按jsdm把gs 连接起来成为表b 中的样子

[yujohny]

--先建立一个合并函数
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

[yujohny]

--先建立一个合并函数
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

[txlicenhe]

这种贴子太多了

--1.创建一个合并的函数
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

[sdhdy]

--用函数
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

[nboys]

利用函数:

create function f_name(@id varchar(100))
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

[nboys]

利用函数:

create function f_name(@jsdm varchar(100))
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 jsdm

select * from b

[zjcxc]

--这个问题太多人问了.
--方法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

[zjcxc]

--方法2.直接生成,适合7.0/2000

--生成表b
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)