日期:2014-05-17 浏览次数:20631 次
WITH a1([字段1],[字段2]) AS
(
SELECT 'a','张三' UNION ALL
SELECT 'a','李四' UNION ALL
SELECT 'b','张三' UNION ALL
SELECT 'b','王五' UNION ALL
SELECT 'c','王五'
)
SELECT [字段1],[字段2]=STUFF(
(SELECT ','+[字段2] FROM a1 WHERE [字段1]=a.[字段1] FOR XML PATH('')),1,1,'')
FROM a1 a
GROUP BY [字段1]
declare @tb table(col1 varchar(10),col2 varchar(10))
insert into @tb(col1,col2)
select 'a','张三'
union all select 'a','李四'
union all select 'b','张三'
union all select 'b','王五'
union all select 'c','王五'
select a.col1,
stuff((select ','+col2 from @tb b
where a.col1=b.col1 for xml path('')),1,1,'') 'col2'
from @tb a
group by col1
/*
col1 col2
a 张三,李四
b 张三,王五
c 王五
*/