--建立测试环境
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
Create Table 表(id varchar(10),re varchar(10))
--插入数据
insert into 表
select '1','aaa' union
select '1','bbb' union
select '1','ccc' union
select '2','pkoge' union
select '2','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,1,8000))
END
GO
select id,dbo.FunMergeCharField(id) as re叠加 from 表 group by id
--删除测试环境
Drop Table 表
Drop FUNCTION FunMergeCharField
Create table TEST
(a Int,
b Varchar(10)
)
GO
--插入数据
Insert TEST Values(1, 'abc')
Insert TEST Values(1, 'hj')
Insert TEST Values(1, 'asdf')
Insert TEST Values(2, 'sdfe')
Insert TEST Values(2, 'asdfaer')
GO
--创建函数
Create Function Getb(@a Int)
RETURNS Varchar(8000)
AS
BEGIN
DECLARE @s Varchar(8000)
SET @s=''
SELECT @s=@s+b FROM TEST WHERE a=@a
RETURN @s
END
GO
--测试
Select a,dbo.Getb(a) As b from TEST Group By a
--删除测试环境
Drop Function Getb
Drop table TEST
--结果
/*
a b
1 abchjasdf
2 sdfeasdfaer
*/