sql-server - SQL - 将多行数据合并为一行和一列

Question

我希望创建一个 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

Answer 1

请尝试:

SELECT T.TaskName, Resources
FROM TaskTable T
CROSS APPLY (
  SELECT 
    STUFF(( SELECT ', ' + R.RESOURCENAME
      FROM [ResourceTable] R
      INNER JOIN [AssignmentTable] A ON R.ResourceUID=A.ResourceUID
      WHERE A.TASKUID = T.TASKUID
      Group by R.RESOURCENAME
      FOR XML PATH('')), 1, 1,'') Resources
) N(Resources)

SQL Fiddle