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 王五 */
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]
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 王五
*/