sql - SQL Server Select 语句中的 COALESCE

Question

我有一个可以分配多个用户的表“Tasks”，存储在表“Assignment”中。

我有 q COALESCE 查询，将我的用户分组到一个字符串中:

DECLARE @Names VARCHAR(8000)

SELECT @Names = COALESCE(@Names, '') + taskAssignment.FullName + '; '
FROM dbo.v_TaskAssignment taskAssignment
WHERE (taskAssignment.TaskId = @TaskId) 
  AND taskAssignment.AssignmentIsRemoved = 'False' 
  AND taskAssignment.FullName IS NOT NULL

SELECT @Names as Names

我的任务查询:

SELECT Id, Summary
FROM dbo.v_Task

我希望我的任务查询有一个字符串列，列出我的所有用户，并用分号分隔。然而，这似乎不起作用:

DECLARE @Names VARCHAR(8000)

SELECT        
    dbo.v_Task.Id, dbo.v_Task.Summary, 
    (SELECT @Names = COALESCE(@Names, '') + taskAssignment.FullName + ';'
     FROM dbo.v_TaskAssignment taskAssignment
     WHERE (taskAssignment.TaskId = dbo.v_Task.Id) 
       AND taskAssignment.AssignmentIsRemoved = 'False' 
       AND taskAssignment.FullName IS NOT NULL
     SELECT @Names as Names) AS Assignements
FROM
    dbo.v_Task 
INNER JOIN
    dbo.v_TaskAssignment ON dbo.v_Task.Id = dbo.v_TaskAssignment.TaskId

我收到以下错误:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ')'.

我如何着手在我的任务选择查询中获取我的 Coalesce 查询？

Answer 1

如果我理解您要正确执行的操作，那么使用 XML PATH 的简单方法怎么样？

SELECT Id, Summary, STUFF(
  (SELECT ';' + FullName 
   FROM v_TaskAssignment ta
   WHERE t.Id = ta.TaskId
     AND ta.AssignmentIsRemoved = 'False' 
     AND ta.FullName IS NOT NULL
   FOR XML PATH ('')), 1, 1, '') Assignments
FROM dbo.v_Task t

An SQLfiddle to test with .