不用游标，如何最高效的完成这类查询？
我有一个表A，里面有两列id和des，现有值如下：
id       des
1           a
1           b
2           a
2           b

如何最简单的得到这样一个表B：
id       des
1           a,b
2           a,b


------解决方案--------------------
顶
------解决方案--------------------
create table TT(id int, des varchar(10))
insert TT select 1, 'a '
union all select 1, 'b '
union all select 2, 'c '
union all select 2, 'd '

create function column_name(@id int)
returns varchar(1000)
as
begin
declare @c1 varchar(4000)

set @c1 = ' '
select @c1=@c1+ ', '+des from tt where id = @id
SELECT @c1=STUFF(@c1, 1, 1, ' ')
return @c1
end

select id , dbo.column_name(id) des from (select distinct id from tt) a
------解决方案--------------------
create table A(id int, des char(1))
insert A select 1, 'a '
union all select 1, 'b '
union all select 2, 'a '
union all select 2, 'b '


select id, des=cast(des as varchar(100)) into #A from A order by id, des

declare @id int, @des varchar(100)
update #A set @des=case when id=@id then @des+ ', '+des else des end,
@id=id,
des=@des


select id, max(des) as des from #A group by id

--result
id des
----------- ----------------------------------------------------------------
1 a,b
2 a,b

(2 row(s) affected)
------解决方案--------------------
create table a
([id] int ,des nvarchar(40))
go

insert into a select 1, 'a '
union all
select 1, 'b '
union all
select 2, 'a '
union all
select 2, 'b '


create function atemp(@id int)
returns varchar(100)
as
begin
declare @c1 varchar(40)
set @c1= ' '
select @c1=@c1+ ', '+des from a where [id]=@id
SELECT @c1=STUFF(@c1, 1, 1, ' ')
return @c1
end

select dbo.atemp([id]) from (Select distinct id from a) b