使用for xml path()进行字符串拼接

Department表

 

 

Employee表

 

 

查询结果：

 

 

查询每个部门的人员名称

首先创建视图查询出各个部门的人员名称:

CREATE VIEW dbo.View_1
AS
SELECT   dbo.Department.DepName, dbo.Employee.EmpName
FROM     dbo.Department INNER JOIN
         dbo.Employee ON dbo.Department.DepID = dbo.Employee.DepID

 然后把查询结果拼接

select DepName,STUFF((select ','+ EmpName  from dbo.View_1  where dbo.View_1.DepName=b.DepName for xml path('')),1,1,'')as name
from dbo.View_1 as b group by DepName