DECLARE @TempTable table(UserID int , UserName nvarchar(50));
insert into @TempTable (UserID,UserName) values (1,'a')
insert into @TempTable (UserID,UserName) values (1,'b')
insert into @TempTable (UserID,UserName) values (1,'c')
insert into @TempTable (UserID,UserName) values (2,'d')
insert into @TempTable (UserID,UserName) values (2,'e')
insert into @TempTable (UserID,UserName) values (2,'f')

--以XML节点式一行存数据，节点名即是列名
select UserID,UserName from @TempTable FOR XML PATH

--PATH中的内容指定根结点名称，若无，则默认为root
select UserID,UserName from @TempTable FOR XML PATH('lzy')

--指定顶级根结点名称
select UserID,UserName from @TempTable FOR XML PATH('lzy'), root ('Root')

--若结果集中无列名，那么节点名也就没有，数据就显示到了一行
select convert(varchar(50),UserName) +','? from @TempTable s WHERE s.UserID = 1

--stuff用来去掉多余的逗号
SELECT DISTINCT t.UserID,
STUFF((select ',' + convert(varchar(50),UserName)? from @TempTable s WHERE s.UserID = t.UserID FOR XML PATH('')),1,1,'')
FROM @TempTable t