create function f_str(@zyzid int,@bz varchar(100))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+convert(varchar,gg)
from 表 where zyzid=@zyzid and bz=@bz
return stuff(@str,1,1,'')
end
go--查询
select id
,zhenghao
,bz
,dbo.f_str(zyzid,bz) as 'gg'
,zyzid
,other
from 表 t
where not exists(select 1
from 表
where zyzid=t.zyzid
and bz=t.bz
and id<t.id)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+convert(varchar,gg)
from 表 where zyzid=@zyzid and bz=@bz
return stuff(@str,1,1,'')
end
go--查询
select id
,zhenghao
,bz
,dbo.f_str(zyzid,bz) as 'gg'
,zyzid
,other
from 表 t
where not exists(select 1
from 表
where zyzid=t.zyzid
and bz=t.bz
and id<t.id)
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret + ',' + rtrim(gg) from tabname
where zhenghao=@zhenghao and bz=@bz and zyzid=@zyzid and other=@other
set @ret = stuff(@ret,1,1,'')
return @ret
end
go
select
id = min(id),
zhenghao,
bz,
gg = dbo.f_str(zhenghao,bz,zyzid,other),
zyzid,
other
from
tabname
group by
zhenghao,bz,zyzid,other
order by
min(id)
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret + ',' + rtrim(gg) from tabname
where zhenghao=@zhenghao and bz=@bz and zyzid=@zyzid and other=@other
set @ret = stuff(@ret,1,1,'')
return @ret
end
go
select
id = min(id),
zhenghao,
bz,
gg = dbo.f_str(zhenghao,bz,zyzid,other),
zyzid,
other
from
tabname
group by
zhenghao,bz,zyzid,other
order by
min(id)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+convert(varchar,gg)
from 表 where zyzid=@zyzid and bz=@bz
return stuff(@str,1,1,'')
end
go
select id,zhenghao,bz,[gg]=dbo.f_str(zhenghao,bz),zyzid,other
from tb a where not exists(select 1 from tb where zhenghao=a.zhenghao and
bz=a.bz and id<a.id)