use Tempdb
go
--> -->
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([部门] nvarchar(1),[姓名] nvarchar(2))
Insert #T
select N'甲',N'甲1' union all
select N'甲',N'甲2' union all
select N'甲',N'甲3' union all
select N'甲',N'甲4' union all
select N'乙',N'乙1' union all
select N'乙',N'乙2' union all
select N'乙',N'乙3'
Go
Select [部门],[姓名]=STUFF((SELECT ','+[姓名] FROM #T WHERE 部门=a.部门 FOR XML PATH('')),1,1,'')
from #T AS a
GROUP BY [部门]
/*
部门 姓名
甲 甲1,甲2,甲3,甲4
乙 乙1,乙2,乙3
*/
SELECT CustomerID, CompanyName
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
'admin';'',Customers);
GO
