------------------------例---------------------------------------
create table temp (id int,colstr varchar(10))
go
insert......
go
create function getstr(@id int)
returns varchar(2000)
as
begin
declare @str varchar(2000)
set @str=''
select @str=@str+','+rtrim(colstr) from temp
where id=@id
set @str=right(@str,len(@str)-1)
return @str
end
go
select id,dbo.getstr(id) 呵呵 from temp group by id
create table temp (id int,colstr varchar(10))
go
insert......
go
create function getstr(@id int)
returns varchar(2000)
as
begin
declare @str varchar(2000)
set @str=''
select @str=@str+','+rtrim(colstr) from temp
where id=@id
set @str=right(@str,len(@str)-1)
return @str
end
go
select id,dbo.getstr(id) 呵呵 from temp group by id
3ku.
But how to create a function like this:create function getstr
(@id int,--Group By Field
@AggregateField varchar(255),-- Field need Aggregate
@TableName varchar(255) --table exists in userdatabase)
go
insert......
go
create function getstr(@id int,@idFild varchar(255),@AggregateField varchar(255),@TableName varchar(255))
returns varchar(2000)
as
begin
declare @str varchar(2000)
set @str=''exec sp_executesql N'select @str=@str+','+rtrim('+@AggregateField+') from '+@TableName+'
where '+@idFild+'='+@id+')', N'@str outputset @str=right(@str,len(@str)-1)
return @str
end
go
select id,dbo.getstr(id,'id','colstr','temp') 哈哈 from temp group by id
哈哈!
U last laugh is more beautify that ever!
Thanx again!!
Can not use 'exec' within a function !!