insert A select 'kai','abc'
insert A select 'kai','def'
insert A select 'kai','ghi'
insert A select 'jim','xxx'
insert A select 'tom','yyy'
create Function dbo.f_A(@col varchar(10))
returns varchar(100)
as
begin
declare @re varchar(100)
set @re=''
select @re=@re+';'+cast(b as varchar)
from A
where a=@col
return(stuff(@re,1,1,''))
end
go
---测试数据
create table T(
f1 varchar(50),
f2 varchar(50)
)
go
insert T select 'A','x'
union select 'A','y'
union select 'B','Y'
union select 'A','Z'
go
--合并处理函数
CREATE FUNCTION dbo.f_str(@f1 varchar(50))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @re varchar(8000)
SET @re=''
SELECT @re=@re+';'+f2
FROM T
WHERE f1=@f1
RETURN(STUFF(@re,1,1,''))
END
go
---调用
select f1, f2=dbo.f_str(f1) from T group by f1