求教一个SQL的写法

[woshizhaoxuhui]

如下的一个表：
name age
jack 20
tom 30
nancy 20

现在我想把所有年龄为20的人的姓名选择到一条记录里，得到这样的结果
jack,nancy
也就是把符合条件的记录中的name字段的值拼接起来，
不知道能否办到？

[發糞塗牆]

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-11-12 15:23:42
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
--	Dec 28 2012 20:23:12 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([name] varchar(5),[age] int)
insert [huang]
select 'jack',20 union all
select 'tom',30 union all
select 'nancy',20
--------------开始查询--------------------------

select a.[age],
stuff((select ','+[name] from [huang] b 
       where b.[age]=a.[age] 
       for xml path('')),1,1,'') 'name'
from [huang] a
WHERE [age]=20
group by  a.[age]
----------------结果----------------------------
/* 
age         name
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
20          jack,nancy
*/

[lzw_0736]

--創建數據
create table #temp([name] varchar(5),[age] int)
insert #temp
select 'jack',20 union all
select 'tom',30 union all
select 'nancy',20

--开始查询
SELECT stuff((select ','+[name] from #temp WHERE [age]=20 for xml path('')),1,1,'') [name]

[小魚人]

create table #tb([name] varchar(5),[age] int)
insert #tb select  'jack',20 union all
select 'tom',30 union all
select 'nancy',20

select * from #tb 
declare @sql varchar(200)=''
select  @sql =@sql+','+name from #tb  where age ='20'
select stuff(@sql ,1,1,'')

drop table #tb

[阳泉酒家小当家]

是这样不：

--drop table tb

create table tb([name] varchar(5),[age] int)

insert tb select  'jack',20 union all
select 'tom',30 union all
select 'nancy',20


select  distinct
        --age,
        stuff(
               (
                 select ','+name
                 from tb t2
                 where t1.age = t2.age
                 for Xml path('')
               ),
               1,1,''
             ) as name
from tb t1  
where age =20
/*
name
jack,nancy
*/

[woshizhaoxuhui]

学习了，谢谢各位的指点！

[t101lian]

if object_id ('tempdb..temp','#aa') is not null
drop table #aa
go 
create table #aa
([name] varchar(5),
[age] int)
 
insert #aa select  'jack',20 union all
select 'tom',30 union all
select 'nancy',20
 
declare @name varchar(20)
select @name=isnull(@name+',','')+ name from #aa where age=20
print @name  
 

/*
jack,nancy
*/

[人间太皮]

引用 6 楼 t101lian 的回复:

if object_id ('tempdb..temp','#aa') is not null
drop table #aa
go 
create table #aa
([name] varchar(5),
[age] int)
 
insert #aa select  'jack',20 union all
select 'tom',30 union all
select 'nancy',20
 
declare @name varchar(20)
select @name=isnull(@name+',','')+ name from #aa where age=20
print @name  
 

/*
jack,nancy
*/

@name长度有限制，不太好。。