--建立测试环境
Create Table t
(uid Int,
f1 Varchar(10),
f2 Varchar(10))
--插入数据
Insert t Values(1, 'aa', 'bb')
Insert t Values(2, 'cc', 'dd')
Insert t Values(2, 'ee', 'ff')
Insert t Values(3, 'gg', 'hh')
GO
--建立函数
Create Function Getf1f2(@uid Int)
Returns Varchar(2000)
As
Begin
Declare @S Varchar(2000)
Set @S=''
Select @S=@S+','+f1+','+f2 from t Where uid=@uid
Return (Stuff(@S,1,1,''))
End
GO
--测试
Select uid,dbo.Getf1f2(uid) As f1f2 from t Group By uid Order By uid
--删除测试环境
Drop Table t
Drop Function Getf1f2
--结果
/*
uid f1f2
1 aa,bb
2 cc,dd,ee,ff
3 gg,hh
*/
Create Table t
(uid Int,
f1 Varchar(10),
f2 Varchar(10))
--插入数据
Insert t Values(1, 'aa', 'bb')
Insert t Values(2, 'cc', 'dd')
Insert t Values(2, 'ee', 'ff')
Insert t Values(3, 'gg', 'hh')
GO
--建立函数
Create Function Getf1f2(@uid Int)
Returns Varchar(2000)
As
Begin
Declare @S Varchar(2000)
Set @S=''
Select @S=@S+','+f1+','+f2 from t Where uid=@uid
Return (Stuff(@S,1,1,''))
End
GO
--测试
Select uid,dbo.Getf1f2(uid) As f1f2 from t Group By uid Order By uid
--删除测试环境
Drop Table t
Drop Function Getf1f2
--结果
/*
uid f1f2
1 aa,bb
2 cc,dd,ee,ff
3 gg,hh
*/
create table test(uid int,f1 varchar(10),f2 varchar(10))
insert into test select 1,'aa','bb'
insert into test select 2,'cc','dd'
insert into test select 2,'ee','ff'
insert into test select 3,'gg','hh'
--创建用户定义函数
create function f_str(@uid int)
returns varchar(8000)
as
begin
declare @ret varchar(8000)
select @ret = @ret +','+f1+','+f2 from test where uid = @uid
return stuff(@ret,1,1,'')
end
--执行查询
select uid,ret=dbo.f_str(uid) from test group by uid order by uid--输出结果
uid ret
--- ------------
1 aa,bb
2 cc,dd,ee,ff
3 gg,hh
表t为表变量,应该如何传到Getf1f2中去呢?
--------------------------------------
这么处理一下。--建立函数
Create Function Getf1f2(@uid Int)
Returns Varchar(2000)
As
Begin
Declare @S Varchar(2000)
Set @S=''
Select @S=@S+','+f1+','+f2 from TEMPTable Where uid=@uid
Return (Stuff(@S,1,1,''))
End
GO
--建立测试环境
Declare @t Table
(uid Int,
f1 Varchar(10),
f2 Varchar(10))
--插入数据
Insert @t Values(1, 'aa', 'bb')
Insert @t Values(2, 'cc', 'dd')
Insert @t Values(2, 'ee', 'ff')
Insert @t Values(3, 'gg', 'hh')
--测试
Select * Into TEMPTable from @t
Select uid,dbo.Getf1f2(uid) As f1f2 from @t Group By uid Order By uid
Drop Table TEMPTable
--删除测试环境
Drop Function Getf1f2
--结果
/*
uid f1f2
1 aa,bb
2 cc,dd,ee,ff
3 gg,hh
*/