create function f_c(@id int) --创建函数
returns varchar(8000)
as
begin
declare @area varchar(8000)
set @area=''
select @area=@area+area+',' from tab where id=@id
return left(@area,len(@area)-1)
end
goselect id,dbo.f_c(id) from tab group by id
returns varchar(8000)
as
begin
declare @area varchar(8000)
set @area=''
select @area=@area+area+',' from tab where id=@id
return left(@area,len(@area)-1)
end
goselect id,dbo.f_c(id) from tab group by id
CREATE TABLE tab(ID int,Area varchar(10))
INSERT tab SELECT 1,'A'
UNION ALL SELECT 1,'B'
UNION ALL SELECT 1,'C'
UNION ALL SELECT 2,'D'
UNION ALL SELECT 2,'E'
UNION ALL SELECT 3,'F'GO--合并处理函数
CREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(100)
AS
BEGIN
DECLARE @re varchar(800)
SET @re=''
SELECT @re=@re+','+Area
FROM tab
WHERE ID=@id
RETURN(STUFF(@re,1,1,''))
END
GO--调用函数
SELECT id,Area=dbo.f_str(id) FROM tab GROUP BY id
--删除测试
DROP TABLE tb
DROP FUNCTION f_str---结果
id Area
--------------
1 A,B,C
2 D,E
3 F
INSERT tab SELECT 1,'A'
UNION ALL SELECT 1,'B'
UNION ALL SELECT 1,'C'
UNION ALL SELECT 2,'D'
UNION ALL SELECT 2,'E'
UNION ALL SELECT 3,'F'GO
declare @i int
declare @ii varchar(100)
set @ii = ''
declare dd cursor for select distinct id from tab
open dd
fetch dd into @i
WHILE @@FETCH_STATUS = 0
BEGIN
select @ii=@ii+area from tab where id = @i
print convert(varchar(10),@i)+ ' ' +@ii
set @ii = ''
fetch dd into @i
END
CLOSE dd
DEALLOCATE dddrop table tab/*
1 ABC
2 DE
3 F*/