求一sql，有点难度，等待...

[sunmingdong]

注意：出于执行效率的考虑，不用游标（循环），

表A
ID(主键) Name
001 lid
002 like

表B
ID(FK) Memos
001 football
001 sing
001 internet

查询结果需要是
ID Name Memos
001 lid football,sing,internet

[sunmingdong]

谢了！大家已经说的很清楚了，但我的实际情况是用在sybase数据库中，不支持自定义函数

欢迎继续讨论！http://expert.csdn.net/Expert/topic/2251/2251034.xml?temp=6.042117E-02

[playyuer]

too old

[txlicenhe]

--1.创建一个合并的函数
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+Memos from 表B where ID=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go

--调用自定义函数得到结果
select id,name,dbo.fmerg(id) from 表A
where id in (select distinct id from 表B)

[happydreamer]

create function getresult(@ID varchar(10))
returns varchar(100)
as
begin
declare @result varchar(100)
set @result=''
select @result=@result+Name from B where ID=@ID
return @result
end

语句：
select id,[name] ,dbo.getresult(ID) from A
where id in (select distinct id from B)

[sunmingdong]

这么多高手，我先试试，试好了就结帐

[zjcxc]

用自定义函数,如果是7.0,用临时表来处理:

[aierong]

create table a ( id varchar(100),name varchar(100))
insert into a values('001','lid')
insert into a values('002','like')
insert into a values('001','d')


create table b(id varchar(100), Memos varchar(100))
insert into b values('001', 'football')
insert into b values('001', 'sing')


create function fun_1(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+Memos from b where ID=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go


select id,dbo.fun_1(id) as name into #tem
from b
group by id
go

select a.id,a.name,t.name
from a join #tem as t on a.id=t.id
go

[CrazyFor]

1,建自定义函数
Create function getstr(@id Nchar(100))
returns Nvarchar(4000)
as
begin
declare @str Nvarchar(4000),@分隔符 Nvarchar(10)
set @分隔符=N','
set @str=N''
select @str=@str+rtrim(Col)+@分隔符 from 表名
--------------------------------^^^^要相加的字段名
where 相加条件字段=@id
set @str=left(@str,len(@str)-1)落 ---却除最后一个分隔符
return @str
end
GO

2,调用
select 条件字段,dbo.getstr(条件字段) from 表名 group by 条件字段