紧急求救:关于函数中的一个问题

zlj303 2008-03-04 09:32:38
必须用一个函数(不能写存储),将多行中的某个字段合并成一行
例:id name
1 a
2 b
3 c
4 d

如果参数@id='1,3,4' 返回a,c,d

create function fn_Merge(@id varchar(50))
returns varchar(8000)
as
begin

declare @str varchar(8000)

set @str= ''
select @str=@str+name+ ', ' from table where id in (@id)
return(@str)
End
go

可是问题是不能将@id转为int型,我将id 转为 cast(id as varchar(50))虽然不报错了,可是得不到结果,
用临时表,函数又提示不支持临时表。

谁又好的方法,请帮帮我。
...全文
76 点赞 收藏 11
写回复
11 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
-狙击手- 2008-03-04
create function fn_Merge(@id varchar(50))
returns varchar(8000)
as
begin

declare @str varchar(8000)

set @str= ''
select @str=@str+name+ ', ' from table where id in (@id)
return(@str)
End
go

--

这种写得用动态 SQL



回复
lhsxsh 2008-03-04
create table a(id int,name varchar(10))
go
insert into a values(1,'a')
insert into a values(2,'b')
insert into a values(3,'c')
insert into a values(4,'d')
go


create function fn_Merge(@id varchar(50))
returns varchar(8000)
as
begin
declare @str varchar(8000)
select @str=isnull(@str+',','')+name from a where charindex(','+ltrim(id)+',',','+@id+',')>0
return @str
End
go

select dbo.fn_Merge('1,3,4')


charindex很好我也有时常这个
回复
zlj303 2008-03-04
pt1314917 谢谢你可以了
回复
pt1314917 2008-03-04

create table a(id int,name varchar(10))
go
insert into a values(1,'a')
insert into a values(2,'b')
insert into a values(3,'c')
insert into a values(4,'d')
go


create function fn_Merge(@id varchar(50))
returns varchar(8000)
as
begin
declare @str varchar(8000)
select @str=isnull(@str+',','')+name from a where charindex(','+ltrim(id)+',',','+@id+',')>0
return @str
End
go

select dbo.fn_Merge('1,3,4')

--结果:
a,c,d

--楼主,咋不行呢?
回复
xiaoliaoyun 2008-03-04
我的方法比较麻烦,另外写了个函数:

create table a(id int,name varchar(10))
go
insert into a values(1,'a')
insert into a values(2,'b')
insert into a values(3,'c')
insert into a values(4,'d')
go

create function f_split_string
(
@string varchar(8000)
)
returns @id_table table (object_id int, order_no int)
as
begin
declare @object_id int
declare @i int
declare @len int
declare @loop_count int
declare @string_tmp varchar(8000);

if (@string is null) or (ltrim(@string) = '')
return

set @loop_count = 1
set @string_tmp = rtrim(@string)

while charindex(',',@string_tmp) > 0
begin
set @len = len(@string_tmp)
set @i = charindex(',', @string_tmp)
set @object_id = cast(left(@string_tmp, @i-1) as int)

insert into @id_table (object_id, order_no) values (@object_id, @loop_count)
set @string_tmp = right(@string_tmp, @len - @i)

set @loop_count = @loop_count + 1
end

set @object_id = cast(@string_tmp as int)
insert into @id_table (object_id, order_no) values (@object_id, @loop_count)

return
end
go

create function fn_merge(@id varchar(50))
returns varchar(8000)
as
begin
declare @str varchar(8000)

if (@id is null) or (ltrim(@id) = '')
return ''

set @str= ''
select @str=@str + name + ', ' from a where id in (select object_id from dbo.f_split_string(@id))
set @str = left(@str,len(@str) - 1)
return(@str)
end
go

select dbo.fn_merge('1,3,4')

drop table a
drop function f_split_string
drop function fn_merge
回复
zlj303 2008-03-04
还有我传过去的参数肯定是字符串形式的,即@id='1,3,4'
回复
zlj303 2008-03-04
pt1314917,你的方法提示信息一样,服务器: 消息 245,级别 16,状态 1,行 18
将 varchar 值 '1,3,4转换为数据类型为 int 的列时发生语法错误。
回复
pt1314917 2008-03-04
2楼的可以么?
回复
zlj303 2008-03-04
dawugui,临时表不行啊,我试过,说函数不支持临时表
回复
pt1314917 2008-03-04

create function fn_Merge(@id varchar(50))
returns varchar(8000)
as
begin

declare @str varchar(8000)
select @str=isnull(@str+',','')+name from table where charindex(','+ltrim(id)+',',','+@id+',')>0
return @str
End
go

回复
dawugui 2008-03-04
create table tb(col varchar(20))
insert tb values ('a')
insert tb values ('b')
insert tb values ('c')
insert tb values ('d')
insert tb values ('e')
go

--方法一
declare @sql varchar(1000)
set @sql = ''
select @sql = @sql + t.col + ',' from (select col from tb) as t
set @sql='select result = ''' + @sql + ''''
exec(@sql)
/*
result
----------
a,b,c,d,e,
*/

--方法二
declare @output varchar(8000)
select @output = coalesce(@output + ',' , '') + col from tb
print @output
/*
a,b,c,d,e
*/

drop table tb
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-03-04 09:32
社区公告
暂无公告