create function f_1(@str varchar(20))
returns varchar(4000)
as
begin
declare @s varchar(4000)
set @s=''
select @s=@s+','+f2 from tb where f1=@str
return stuff(@s,1,1,'')
end
go
select f1,dbo.f_1(f1) as 'f2' from tb
go
returns varchar(4000)
as
begin
declare @s varchar(4000)
set @s=''
select @s=@s+','+f2 from tb where f1=@str
return stuff(@s,1,1,'')
end
go
select f1,dbo.f_1(f1) as 'f2' from tb
go
select f1,dbo.f_1(f1) as 'f2' from tb
group by f1
CREATE TABLE tb(F1 varchar(10),F2 varchar(10))
INSERT tb SELECT 'a','f'
UNION ALL SELECT 'b','b'
UNION ALL SELECT 'a','g'
UNION ALL SELECT 'c','j'
UNION ALL SELECT 'b','o'
GO--合并处理函数
CREATE FUNCTION dbo.f_str(@col1 varchar(10))
RETURNS varchar(100)
AS
BEGIN
DECLARE @re varchar(100)
SET @re=''
SELECT @re=@re+','+F2
FROM tb
WHERE F1=@col1
RETURN(STUFF(@re,1,1,''))
END
GO--调用函数
SELECT F1,F2=dbo.f_str(F1) FROM tb GROUP BY F1
--删除测试
DROP TABLE tb
DROP FUNCTION f_str
/*--结果
F1 F2
a f,g
b b,o
c j
--*/
GO