--建立测试环境
Create Table 表(id varchar(10),amount integer,re varchar(10))
--插入数据
insert into 表
select '1','3','aaa' union
select '1','5','bbb' union
select '1','4','ccc' union
select '2','10','pkoge' union
select '2','12','daf'
go
--测试语句
CREATE FUNCTION FunMergeCharField(@vchA varchar(10))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r=''
SELECT @r=@r+','+re FROM 表 WHERE id=@vchA
RETURN(substring(@r,2,8000))
END
GO
select id,sum(amount) as sum,dbo.FunMergeCharField(id) as re叠加 from 表 group by id
--删除测试环境
Drop Table 表
Drop FUNCTION FunMergeCharField
Create Table 表(id varchar(10),amount integer,re varchar(10))
--插入数据
insert into 表
select '1','3','aaa' union
select '1','5','bbb' union
select '1','4','ccc' union
select '2','10','pkoge' union
select '2','12','daf'
go
--测试语句
CREATE FUNCTION FunMergeCharField(@vchA varchar(10))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r=''
SELECT @r=@r+','+re FROM 表 WHERE id=@vchA
RETURN(substring(@r,2,8000))
END
GO
select id,sum(amount) as sum,dbo.FunMergeCharField(id) as re叠加 from 表 group by id
--删除测试环境
Drop Table 表
Drop FUNCTION FunMergeCharField
insert into tb
select 1, 'a', '2008-1-1' union all
select 1, 'b', '2008-1-2' union all
select 2, 'c', '2008-1-3' union all
select 2, 'd', '2008-1-4' union all
select 1, 'e', '2008-1-5'
select * from tb
gocreate function f_str(@id int)
returns varchar(100)
as
begin
declare @str varchar(100)
set @str=''
select @str=@str+','+val from tb where id=@id
set @str=stuff(@str,1,1,'')return (@str)
endgoselect id,dbo.f_str(id) from tb group by id /*
id
--------------
1 a,b,e
2 c,d
*/drop function dbo.f_str
drop table tb
(
id int,
zimu nvarchar(20)
)
insert @tab select 1,'a'
union select 1,'b'
select * from @tabdeclare @group nvarchar(4000)
set @group=''
SELECT @group=@group+','+zimu FROM @tab where id=1
select substring(@group,2,4000)