---测试数据 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---结果 f1 f2 A x;y;Z B Y
create table A ( a varchar(10), b varchar(10) )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 goselect a,b=dbo.f_A(a) from a group by a
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---结果
f1 f2 A x;y;Z
B Y
(
a varchar(10),
b varchar(10)
)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
goselect a,b=dbo.f_A(a) from a group by a