Create Function GetOpID(@quesid nvarchar(10))
returns nvarchar(1000)
as
begin
declare @s nvarchar(1000)
set @s=''
select @s=@s+','+ltrim(opid) from 表名 where quesid=@quesid
set @s=stuff(@s,1,1,'')
return(@s)
end
go
Create Function GetContent(@quesid nvarchar(10))
returns nvarchar(1000)
as
begin
declare @s nvarchar(1000)
set @s=''
select @s=@s+','+ltrim(content) from 表名 where quesid=@quesid
set @s=stuff(@s,1,1,'')
return(@s)
end
go
Select quesid,dbo.GetOpID(quesid),dbo.GetContent(quesid) from 表名 Group By quesid
--建立测试环境
Create Table 表(quesid varchar(10),opid varchar(10),content varchar(10))
--插入数据
insert into 表
select '34','22','a' union
select '34','23','b' union
select '34','24','c' union
select '35','25','d' union
select '35','26','e' union
select '35','27','f' union
select '36','28','g' union
select '36','29','h'
go
--测试语句
CREATE FUNCTION FunMergeCharField(@vchA varchar(10) )
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r=''
SELECT @r=@r+','+opid FROM 表 WHERE quesid=@vchA
RETURN(substring(@r,2,8000))
END
go
--删除测试环境
select quesid,dbo.FunMergeCharField(quesid) from 表 group by quesid
Drop Table 表
Drop Function FunMergeCharField