求教一个SQL的写法
如下的一个表：
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
*/
--創建數據
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
*/
