请教，这样的要求能否用一条SQL解决？

[山野村夫]

表：
========================
Title Author
book2 b1
book3 c1
book1 a1
book1 a3
book2 b2
book1 a2
========================
返回结果：
Title Author
book1 a1,a2,a3
book2 b1,b2
book3 c1
======================

查询相同Title的Author，Author要排序

[LouisXIV]

一句完成要写用户自定义函数

[paoluo]

Create Table TEST
(Title Varchar(10),
Author Varchar(10))
Insert TEST Select 'book2', 'b1'
Union All Select 'book3', 'c1'
Union All Select 'book1', 'a1'
Union All Select 'book1', 'a3'
Union All Select 'book2', 'b2'
Union All Select 'book1', 'a2'
GO
Create Function GetAuthor(@Title Varchar(10))
Returns Varchar(1000)
As
Begin
Declare @S Varchar(1000)
Set @S=''
Select @S=@S+','+Author From TEST Where Title=@Title Order By Author
Return(Stuff(@S,1,1,''))
End
GO
Select Title,dbo.GetAuthor(Title) As Author From TEST Group By Title
GO
Drop Table TEST
Drop Function GetAuthor
--Result
/*
Title Author
book1 a1,a2,a3
book2 b1,b2
book3 c1
*/

[liangpei2008]

create table 表(Title varchar(20),Author varchar(20))
insert into 表 select 'book2','b1'
insert into 表 select 'book3','c1'
insert into 表 select 'book1','a1'
insert into 表 select 'book1','a3'
insert into 表 select 'book2','b2'
insert into 表 select 'book1','a2'
go

--创建用户定义函数
create function f_str(@Title varchar(20))
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret+','+Author from 表 where title = @title
set @ret = stuff(@ret,1,1,'')
return @ret
end
go


--执行
select title,author=dbo.f_str(title) from 表 group by title order by title

[LouisXIV]

if exists (select 1 from sysobjects where id=object_id('test')and xtype='U') drop table test
create table test
(
title varchar(5),
auther varchar(3)
)
insert into test
select 'book2','b1' union all
select 'book3','c1' union all
select 'book1','a1' union all
select 'book1','a3' union all
select 'book2','b2' union all
select 'book1','a2'
go

create function dbo.f_test(@a varchar(6))
returns varchar(40)
as
begin
declare @b varchar(40)
select @b=''
select @b=@b+','+auther from test
where title=@a
set @b=right(@b,len(@b)-1)
return @b
end
go

select title,dbo.f_test(title) as auther
from test
group by title
order by title

drop table test
drop function f_test

/*

title auther
----- ----------------------------------------
book1 a1,a3,a2
book2 b1,b2
book3 c1
*/

[山野村夫]

好快，呵呵
这么快就给出答案，谢谢

[paoluo]

。。。

[十一月猪]

create table tttttt
(title varchar(5) , author varchar(2))

insert into tttttt
select 'book2', 'b1' union
select 'book3', 'c1' union
select 'book1', 'a1' union
select 'book1', 'a3' union
select 'book2', 'b2' union
select 'book1', 'a2'




create function f_union2(@title varchar(5))
returns varchar(8000)
as
begin
declare @s varchar(8000)
set @s = ''

select @s = @s + ','+ author
from tttttt
where title = @title
order by author asc

select @s = stuff(@s , 1, 1 , '')

return @s
end