函数--建立函数 create function fn_getf2(@f1 int) returns varchar(300) as begin declare @r varchar(300) set @r='' select @r=@r+f2 from t1 where f1=@f1 return @r end go--调用 select f1,dbo.fn_getf2(f1) as f2 from t1 group by f1
在SQL Server 2000中,使用函数处理是最简单的方法,如楼上。也可以使用游标处理。
if object_id('pubs..tb') is not null drop table tb gocreate table tb ( f1 varchar(10), f2 varchar(10) )insert into tb(f1,f2) values('1', 'a') insert into tb(f1,f2) values('1', 'b') insert into tb(f1,f2) values('2', 'c') insert into tb(f1,f2) values('2', 'd') insert into tb(f1,f2) values('2', 'e') insert into tb(f1,f2) values('3', 'f') go--创建一个合并的函数 create function f_hb(@f1 varchar(10)) returns varchar(8000) as begin declare @str varchar(8000) set @str = '' select @str = @str + cast(f2 as varchar) from tb where f1 = @f1 set @str = right(@str , len(@str)) return(@str) End go--调用自定义函数得到结果: select distinct f1 ,dbo.f_hb(f1) as f2 from tbdrop table tbf1 f2 ---------- --- 1 ab 2 cde 3 f(所影响的行数为 3 行)
SELECT * FROM( SELECT DISTINCT f1 FROM tb )A OUTER APPLY( SELECT [values]= REPLACE(STUFF(REPLACE(REPLACE( ( SELECT f2 FROM tb N WHERE f1= A.f1 FOR XML AUTO ), '<N f2="', ','), '"/>', ''), 1, 1, ''),',','') )N /* f1 values ---------- ---------- 1 ab 2 cde 3 f */
--借用一下老乌龟的数据 create table tb ( f1 varchar(10), f2 varchar(10) )insert into tb(f1,f2) values('1', 'a') insert into tb(f1,f2) values('1', 'b') insert into tb(f1,f2) values('2', 'c') insert into tb(f1,f2) values('2', 'd') insert into tb(f1,f2) values('2', 'e') insert into tb(f1,f2) values('3', 'f') go create function combine(@f1 varchar(10)) returns varchar(8000) as begin declare @str varchar(8000) set @str = '' select @str = @str + f2 from tb where f1 = @f1 return @str End goselect f1 ,dbo.combine(f1) as f2 from tb group by f1drop table tb--- 1 ab 2 cde 3 f
create function fn_getf2(@f1 int)
returns varchar(300)
as
begin
declare @r varchar(300)
set @r=''
select @r=@r+f2 from t1 where f1=@f1
return @r
end
go--调用
select f1,dbo.fn_getf2(f1) as f2
from t1
group by f1
drop table tb
gocreate table tb
(
f1 varchar(10),
f2 varchar(10)
)insert into tb(f1,f2) values('1', 'a')
insert into tb(f1,f2) values('1', 'b')
insert into tb(f1,f2) values('2', 'c')
insert into tb(f1,f2) values('2', 'd')
insert into tb(f1,f2) values('2', 'e')
insert into tb(f1,f2) values('3', 'f')
go--创建一个合并的函数
create function f_hb(@f1 varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + cast(f2 as varchar) from tb where f1 = @f1
set @str = right(@str , len(@str))
return(@str)
End
go--调用自定义函数得到结果:
select distinct f1 ,dbo.f_hb(f1) as f2 from tbdrop table tbf1 f2
---------- ---
1 ab
2 cde
3 f(所影响的行数为 3 行)
FROM(
SELECT DISTINCT
f1
FROM tb
)A
OUTER APPLY(
SELECT
[values]= REPLACE(STUFF(REPLACE(REPLACE(
(
SELECT f2 FROM tb N
WHERE f1= A.f1
FOR XML AUTO
), '<N f2="', ','), '"/>', ''), 1, 1, ''),',','')
)N
/*
f1 values
---------- ----------
1 ab
2 cde
3 f
*/
create table tb
(
f1 varchar(10),
f2 varchar(10)
)insert into tb(f1,f2) values('1', 'a')
insert into tb(f1,f2) values('1', 'b')
insert into tb(f1,f2) values('2', 'c')
insert into tb(f1,f2) values('2', 'd')
insert into tb(f1,f2) values('2', 'e')
insert into tb(f1,f2) values('3', 'f')
go
create function combine(@f1 varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + f2 from tb where f1 = @f1
return @str
End
goselect f1 ,dbo.combine(f1) as f2 from tb group by f1drop table tb---
1 ab
2 cde
3 f