使用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