我想创建一个SQL Server查询,使用三个表将具有相同任务的资源组合成一行/记录字符串。我的下面的SQL Server查询似乎无法正常工作,并且需要很长时间才能执行,然后出现错误。谢谢!
任务表
TaskUID
TaskName
任务分配表
TaskUID
ResourceUID
ResourceUID
ResourceName
之前
**Task Name Resource Name**
Weapon Launch Amy
Weapon Launch Sam
Weapon Launch Marisa
Weapon Launch Katy
Weapon Launch John
Sweating Tears Marisa
Sweating Tears Joe
Sweating Tears Katy
Sweating Tears Michael
Ramp Diver Joe
Ramp Diver Michael
之后
**Task Name Resource Name**
Weapon Launch Amy; Sam; Marisa; Katy; John
Sweating Tears Marisa; Joe; Katy; Michael
Ramp Diver Michael; Joe
查询
SELECT T.TaskName,
STUFF(( SELECT ', ' + R.RESOURCENAME
FROM
[Resource Table] R
LEFT JOIN [Assignment Table] A ON R.ResourceUID=A.ResourceUID
WHERE
A.TASKUID=T.TaskUID
Group by R.RESOURCENAME
FOR XML PATH('')), 1, 1,'') Resources
FROM [Task Table] T
INNER JOIN [Assignment Table] A ON T.TASKUID=A.TASKUID